Make sure we're using indexes during rollbacks
Fixes https://github.com/IntersectMBO/cardano-db-sync/issues/2083#issuecomment-4110278201
for 13.6.0.5.
The planner sometimes uses a query similar to
Index Scan using tx_pkey on tx
Filter: (block_id >= $1)
the filter is not Index Cond, so this ends up in a sequential scan.
The index refers to the primary key and is only used for sorting.
Instead we want to use
Sort (top-N heapsort)
-> Index Scan using idx_tx_block_id
Index Cond: (block_id >= $1)
With this change we're forcing a similar plan
Aggregate
-> Index Scan using idx_tx_block_id
Index Cond: (block_id >= $1)
making sure we're using the Index Cond and then sorting.