updated query to use l2_keystones rather than pop_basis in JOIN to improve performance

[ClaytonNorthey92]

Summary

tldr: use l2_keystones in JOIN to avoid page reads of pop_basis

updated query to use l2_keystones rather than pop_basis in JOIN to improve performance

Changes

Here are the before and after query plans; neither are great, but the after is much better.

note the differences between costs and "actual time"s.

when using the l2_keystones result instead of pop_basis, we can use an "Index only scan" for pop_basis. This means we don't have to pull any values from the pop_basis records after the join because the join itself uses the index.

Previously we had to use the result of the pop_basis JOIN to then read the table data then pull l2_keystone_abrev_hash from that. When we use l2_keystones instead, we never have to read the table data, we only need the index itself.

Our cost for this query for the exemplified l2 keystone '\x0013d9736f664d0780583182468bd6d74d4b38234106950d87b14271230ef47f goes from cost=520904.80..520911.52 to cost=34745.71..34752.43

and the timing goes from actual time=5980.838..5980.848 to actual time=151.756..151.762 ❗

before

                                                                                               QUERY PLAN                                                                   

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------
 Sort  (cost=520904.80..520911.52 rows=2687 width=242) (actual time=5980.838..5980.848 rows=7 loops=1)
   Sort Key: l2_keystones.l2_block_number DESC
   Sort Method: quicksort  Memory: 26kB
   InitPlan 2 (returns $3)
     ->  Limit  (cost=0.29..0.32 rows=1 width=8) (actual time=0.061..0.063 rows=1 loops=1)
           ->  Index Only Scan Backward using btc_blocks_can_height_idx on btc_blocks_can btc_blocks_can_2  (cost=0.29..2344.06 rows=89451 width=8) (actual time=0.055..0.05
6 rows=1 loops=1)
                 Heap Fetches: 1
   ->  Hash Left Join  (cost=3782.45..520751.43 rows=2687 width=242) (actual time=1682.188..5980.797 rows=7 loops=1)
         Hash Cond: (pop_basis.btc_block_hash = btc_blocks_can.hash)
         ->  Nested Loop Left Join  (cost=41.80..10617.98 rows=2687 width=218) (actual time=585.866..585.906 rows=7 loops=1)
               ->  Index Scan using l2_keystones_pkey on l2_keystones  (cost=0.41..8.43 rows=1 width=185) (actual time=0.026..0.028 rows=1 loops=1)
                     Index Cond: (l2_keystone_abrev_hash = '\x0013d9736f664d0780583182468bd6d74d4b38234106950d87b14271230ef47f'::bytea)
               ->  Bitmap Heap Scan on pop_basis  (cost=41.39..10582.68 rows=2687 width=66) (actual time=0.102..0.129 rows=7 loops=1)
                     Recheck Cond: (l2_keystone_abrev_hash = '\x0013d9736f664d0780583182468bd6d74d4b38234106950d87b14271230ef47f'::bytea)
                     Heap Blocks: exact=7
                     ->  Bitmap Index Scan on l2_keystone_abrev_hash_idx  (cost=0.00..40.72 rows=2687 width=0) (actual time=0.084..0.084 rows=7 loops=1)
                           Index Cond: (l2_keystone_abrev_hash = '\x0013d9736f664d0780583182468bd6d74d4b38234106950d87b14271230ef47f'::bytea)
         ->  Hash  (cost=2622.51..2622.51 rows=89451 width=41) (actual time=81.781..81.782 rows=89451 loops=1)
               Buckets: 131072  Batches: 1  Memory Usage: 8013kB
               ->  Seq Scan on btc_blocks_can  (cost=0.00..2622.51 rows=89451 width=41) (actual time=0.114..41.683 rows=89451 loops=1)
         SubPlan 1
           ->  Limit  (cost=32.80..188.45 rows=1 width=8) (actual time=758.941..758.942 rows=1 loops=7)
                 ->  Nested Loop  (cost=32.80..694363482.70 rows=4461153 width=8) (actual time=758.939..758.939 rows=1 loops=7)
                       ->  Nested Loop  (cost=32.38..693972124.49 rows=13383967 width=41) (actual time=0.349..601.164 rows=36292 loops=7)
                             ->  Index Scan using btc_blocks_can_height_idx on btc_blocks_can btc_blocks_can_1  (cost=0.29..4061.06 rows=89451 width=41) (actual time=0.031.
.34.488 rows=55975 loops=7)
                             ->  Bitmap Heap Scan on pop_basis pop_basis_1  (cost=32.09..7734.65 rows=2343 width=66) (actual time=0.007..0.008 rows=1 loops=391825)
                                   Recheck Cond: (btc_blocks_can_1.hash = btc_block_hash)
                                   Heap Blocks: exact=110124
                                   ->  Bitmap Index Scan on btc_block_hash_idx  (cost=0.00..31.50 rows=2343 width=0) (actual time=0.007..0.007 rows=1 loops=391825)
                                         Index Cond: (btc_block_hash = btc_blocks_can_1.hash)
                       ->  Memoize  (cost=0.42..7.10 rows=1 width=33) (actual time=0.004..0.004 rows=0 loops=254044)
                             Cache Key: pop_basis_1.l2_keystone_abrev_hash
                             Cache Mode: logical
                             Hits: 83531  Misses: 170513  Evictions: 146154  Overflows: 0  Memory Usage: 2308kB
                             ->  Index Scan using l2_keystones_pkey on l2_keystones ll  (cost=0.41..7.09 rows=1 width=33) (actual time=0.005..0.005 rows=0 loops=170513)
                                   Index Cond: (l2_keystone_abrev_hash = pop_basis_1.l2_keystone_abrev_hash)
                                   Filter: (l2_block_number >= l2_keystones.l2_block_number)
                                   Rows Removed by Filter: 0
 Planning Time: 1.395 ms
 JIT:
   Functions: 35
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 5.714 ms, Inlining 34.702 ms, Optimization 340.340 ms, Emission 210.951 ms, Total 591.706 ms
 Execution Time: 5987.114 ms
(44 rows)

after

                                                                                             QUERY PLAN                                                                     

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------
 Sort  (cost=34745.71..34752.43 rows=2687 width=242) (actual time=151.756..151.762 rows=7 loops=1)
   Sort Key: l2_keystones.l2_block_number DESC
   Sort Method: quicksort  Memory: 26kB
   InitPlan 2 (returns $3)
     ->  Limit  (cost=0.29..0.32 rows=1 width=8) (actual time=0.036..0.036 rows=1 loops=1)
           ->  Index Only Scan Backward using btc_blocks_can_height_idx on btc_blocks_can btc_blocks_can_2  (cost=0.29..2341.07 rows=89452 width=8) (actual time=0.035..0.03
5 rows=1 loops=1)
                 Heap Fetches: 0
   ->  Hash Left Join  (cost=3782.47..34592.35 rows=2687 width=242) (actual time=150.521..151.740 rows=7 loops=1)
         Hash Cond: (pop_basis.btc_block_hash = btc_blocks_can.hash)
         ->  Nested Loop Left Join  (cost=41.80..10617.99 rows=2687 width=218) (actual time=0.068..0.181 rows=7 loops=1)
               ->  Index Scan using l2_keystones_pkey on l2_keystones  (cost=0.41..8.43 rows=1 width=185) (actual time=0.012..0.014 rows=1 loops=1)
                     Index Cond: (l2_keystone_abrev_hash = '\x0013d9736f664d0780583182468bd6d74d4b38234106950d87b14271230ef47f'::bytea)
               ->  Bitmap Heap Scan on pop_basis  (cost=41.39..10582.69 rows=2687 width=66) (actual time=0.052..0.157 rows=7 loops=1)
                     Recheck Cond: (l2_keystone_abrev_hash = '\x0013d9736f664d0780583182468bd6d74d4b38234106950d87b14271230ef47f'::bytea)
                     Heap Blocks: exact=7
                     ->  Bitmap Index Scan on l2_keystone_abrev_hash_idx  (cost=0.00..40.72 rows=2687 width=0) (actual time=0.039..0.040 rows=7 loops=1)
                           Index Cond: (l2_keystone_abrev_hash = '\x0013d9736f664d0780583182468bd6d74d4b38234106950d87b14271230ef47f'::bytea)
         ->  Hash  (cost=2622.52..2622.52 rows=89452 width=41) (actual time=147.854..147.855 rows=89452 loops=1)
               Buckets: 131072  Batches: 1  Memory Usage: 8013kB
               ->  Seq Scan on btc_blocks_can  (cost=0.00..2622.52 rows=89452 width=41) (actual time=0.019..84.188 rows=89452 loops=1)
         SubPlan 1
           ->  Limit  (cost=1.27..7.52 rows=1 width=8) (actual time=0.421..0.421 rows=1 loops=7)
                 ->  Nested Loop  (cost=1.27..83695193.15 rows=13384665 width=8) (actual time=0.420..0.420 rows=1 loops=7)
                       ->  Nested Loop  (cost=0.70..5187.66 rows=89452 width=41) (actual time=0.025..0.058 rows=33 loops=7)
                             ->  Index Scan using btc_blocks_can_height_idx on btc_blocks_can btc_blocks_can_1  (cost=0.29..4061.07 rows=89452 width=41) (actual time=0.015.
.0.025 rows=33 loops=7)
                             ->  Materialize  (cost=0.41..8.44 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=231)
                                   ->  Index Scan using l2_keystones_pkey on l2_keystones ll  (cost=0.41..8.43 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=7)
                                         Index Cond: (l2_keystone_abrev_hash = l2_keystones.l2_keystone_abrev_hash)
                                         Filter: (l2_block_number >= l2_keystones.l2_block_number)
                       ->  Index Only Scan using btc_block_hash_idx on pop_basis pop_basis_1  (cost=0.56..912.16 rows=2343 width=33) (actual time=0.010..0.010 rows=0 loops=
231)
                             Index Cond: (btc_block_hash = btc_blocks_can_1.hash)
                             Heap Fetches: 0
 Planning Time: 2.157 ms
 Execution Time: 151.944 ms
(34 rows)