Merge pull request #2085 from IntersectMBO/kderme/fix-slow-rollbacks-13.6.0.5
Make sure we're using indexes during rollbacks 13.6.0.5
Make sure we're using indexes during rollbacks 13.6.0.5
Fixes https://github.com/IntersectMBO/cardano-db-sync/issues/2083
The `queryMinRefId` query uses
```sql
SELECT id FROM <table> WHERE <field> >= $1 ORDER BY id ASC LIMIT 1.
```
The planner sometimes picks a bad plan:
```sql
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 use a simpler query without ORDER BY:
SELECT id FROM <table> WHERE <field> >= $1 LIMIT 10000
This forces the planner to use the field's index.
The results are fetched and the minimum is found in Haskell.
Near the tip this returns only a handful of rows.
If there are more than 10000 matching rows (large rollback),
we fall back to the original ORDER BY id ASC LIMIT 1 query.
Fixes https://github.com/IntersectMBO/cardano-db-sync/issues/2083 The `queryMinRefId` query uses ```sql SELECT id FROM <table> WHERE <field> >= $1 ORDER BY id ASC LIMIT 1. ``` The planner sometimes picks a bad plan: ```sql 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 use a simpler query without ORDER BY: SELECT id FROM <table> WHERE <field> >= $1 LIMIT 10000 This forces the planner to use the field's index. The results are fetched and the minimum is found in Haskell. Near the tip this returns only a handful of rows. If there are more than 10000 matching rows (large rollback), we fall back to the original ORDER BY id ASC LIMIT 1 query.
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.
Fixes https://github.com/IntersectMBO/cardano-db-sync/issues/2083#issuecomment-4110278201
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.
Fix missing Conway genesis
Fixes https://github.com/IntersectMBO/cardano-db-sync/issues/2034 This forces ConwayGenesisFile to always exist.
2041 - fix isolation commits