Probable issue with conversion of body_binary to jsonb
SELECT o.body->'value'->>'permlink' as permlink1, o.block_num, o.id, o.body
FROM hive.operations_view o
WHERE
o.block_num BETWEEN 0 AND 100000000 AND
o.op_type_id = ANY(ARRAY[0, 1, 17, 19, 53, 63, 72, 73]) AND
o.body->'value'->>'author' = 'gtg'
ORDER BY permlink1, o.id
limit 100
this query is problematic (on steem10 there is an index covering this query so its not that long) There is an error:
ERROR: could not find pathkey item to sort
SQL state: XX000
When you run this query without a limit it runs ok (so there is no problem with wrong pathkey). I think it might be a problem with convesion of body_binary because this query runs OK:
SELECT o.body_binary::JSONB->'value'->>'permlink' as permlink, o.block_num, o.id, o.body_binary::JSONB
FROM hive.operations_view o
WHERE
o.block_num BETWEEN 0 AND 100000000 AND
o.op_type_id = ANY(ARRAY[0, 1, 17, 19, 53, 63, 72, 73]) AND
o.body_binary::JSONB->'value'->>'author' = 'blocktrades'
ORDER BY permlink, o.id
limit 100
OFFSET 100