Fix slowdown in live sync

due to changes in btree indexes in postgresql 17 multiple IN statements must be moved to CTE

plan for miss behaving query,

pg16:

"Nested Loop  (cost=51979.78..3427335.04 rows=2977692 width=48) (actual time=1.281..1.594 rows=7 loops=1)"
"  Buffers: shared hit=106 read=8"
"  I/O Timings: shared read=1.055"
"  ->  Limit  (cost=6.32..7.35 rows=1 width=21) (actual time=0.023..0.023 rows=1 loops=1)"
"        Buffers: shared hit=4"
"        ->  Bitmap Heap Scan on contexts hc  (cost=6.32..7.35 rows=1 width=21) (actual time=0.022..0.023 rows=1 loops=1)"
"              Recheck Cond: ((name)::text = 'hafbe_bal'::text)"
"              Heap Blocks: exact=2"
"              Buffers: shared hit=4"
"              ->  Bitmap Index Scan on uq_hive_context_name  (cost=0.00..6.32 rows=1 width=0) (actual time=0.008..0.008 rows=2 loops=1)"
"                    Index Cond: ((name)::text = 'hafbe_bal'::text)"
"                    Buffers: shared hit=2"
"  ->  Append  (cost=51973.46..3375218.07 rows=2977692 width=151) (actual time=1.238..1.527 rows=7 loops=1)"
"        Buffers: shared hit=102 read=8"
"        I/O Timings: shared read=1.055"
"        ->  Subquery Scan on ""*SELECT* 1""  (cost=51973.46..3360298.32 rows=2977691 width=151) (actual time=1.237..1.251 rows=7 loops=1)"
"              Buffers: shared hit=73 read=8"
"              I/O Timings: shared read=1.055"
"              ->  Bitmap Heap Scan on operations ho  (cost=51973.46..3330521.41 rows=2977691 width=157) (actual time=1.237..1.249 rows=7 loops=1)"
"                    Recheck Cond: (((hive.operation_id_to_type_id(id) = ANY ('{40,41,62,32,33,34,59,39,4,20,56,60,52,53,77,70,68}'::integer[])) AND (hive.operation_id_to_block_num(id) <= (LEAST(hc.irreversible_block, hc.current_block_num))) AND (hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279)) OR ((hive.operation_id_to_type_id(id) = 55) AND (hive.operation_id_to_block_num(id) <= (LEAST(hc.irreversible_block, hc.current_block_num))) AND (hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279)) OR ((hive.operation_id_to_type_id(id) = ANY ('{51,63}'::integer[])) AND (hive.operation_id_to_block_num(id) <= (LEAST(hc.irreversible_block, hc.current_block_num))) AND (hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279)))"
"                    Filter: ((hive.operation_id_to_type_id(id) = ANY ('{40,41,62,32,33,34,59,39,4,20,56,60,52,53,77,70,68}'::integer[])) OR ((hive.operation_id_to_type_id(id) = 55) AND (NOT ((((body_binary)::jsonb -> 'value'::text) ->> 'is_saved_into_hbd_balance'::text))::boolean)) OR ((hive.operation_id_to_type_id(id) = ANY ('{51,63}'::integer[])) AND ((((body_binary)::jsonb -> 'value'::text) ->> 'payout_must_be_claimed'::text))::boolean))"
"                    Heap Blocks: exact=1"
"                    Buffers: shared hit=73 read=8"
"                    I/O Timings: shared read=1.055"
"                    ->  BitmapOr  (cost=51973.42..51973.42 rows=3080033 width=0) (actual time=1.214..1.215 rows=0 loops=1)"
"                          Buffers: shared hit=73 read=7"
"                          I/O Timings: shared read=1.043"
"                          ->  Bitmap Index Scan on hive_operations_op_type_id_block_num  (cost=0.00..46738.48 rows=2894313 width=0) (actual time=1.010..1.010 rows=5 loops=1)"
"                                Index Cond: ((hive.operation_id_to_type_id(id) = ANY ('{40,41,62,32,33,34,59,39,4,20,56,60,52,53,77,70,68}'::integer[])) AND (hive.operation_id_to_block_num(id) <= (LEAST(hc.irreversible_block, hc.current_block_num))) AND (hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279))"
"                                Buffers: shared hit=63 read=5"
"                                I/O Timings: shared read=0.864"
"                          ->  Bitmap Index Scan on hive_operations_op_type_id_block_num  (cost=0.00..18.62 rows=1068 width=0) (actual time=0.005..0.005 rows=0 loops=1)"
"                                Index Cond: ((hive.operation_id_to_type_id(id) = 55) AND (hive.operation_id_to_block_num(id) <= (LEAST(hc.irreversible_block, hc.current_block_num))) AND (hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279))"
"                                Buffers: shared hit=4"
"                          ->  Bitmap Index Scan on hive_operations_op_type_id_block_num  (cost=0.00..2983.04 rows=184652 width=0) (actual time=0.195..0.195 rows=2 loops=1)"
"                                Index Cond: ((hive.operation_id_to_type_id(id) = ANY ('{51,63}'::integer[])) AND (hive.operation_id_to_block_num(id) <= (LEAST(hc.irreversible_block, hc.current_block_num))) AND (hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279))"
"                                Buffers: shared hit=6 read=2"
"                                I/O Timings: shared read=0.179"
"        ->  Subquery Scan on ""*SELECT* 2""  (cost=1.06..31.29 rows=1 width=247) (actual time=0.274..0.274 rows=0 loops=1)"
"              Buffers: shared hit=29"
"              ->  Nested Loop  (cost=1.06..31.28 rows=1 width=253) (actual time=0.274..0.274 rows=0 loops=1)"
"                    Join Filter: ((o.fork_id = (max(hbr.fork_id))) AND (hive.operation_id_to_block_num(o.id) = hbr.num))"
"                    Buffers: shared hit=29"
"                    ->  Seq Scan on operations_reversible o  (cost=0.04..30.23 rows=1 width=255) (actual time=0.274..0.274 rows=0 loops=1)"
"                          Filter: ((hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279) AND ((hive.operation_id_to_type_id(id) = ANY ('{40,41,62,32,33,34,59,39,4,20,56,60,52,53,77,70,68}'::integer[])) OR ((hive.operation_id_to_type_id(id) = 55) AND (NOT ((((body_binary)::jsonb -> 'value'::text) ->> 'is_saved_into_hbd_balance'::text))::boolean)) OR ((hive.operation_id_to_type_id(id) = ANY ('{51,63}'::integer[])) AND ((((body_binary)::jsonb -> 'value'::text) ->> 'payout_must_be_claimed'::text))::boolean)))"
"                          Rows Removed by Filter: 27"
"                          Buffers: shared hit=29"
"                    ->  HashAggregate  (cost=1.02..1.03 rows=1 width=12) (never executed)"
"                          Group Key: hbr.num"
"                          ->  Result  (cost=0.00..1.02 rows=1 width=12) (never executed)"
"                                One-Time Filter: (((hc.current_block_num > hc.irreversible_block) AND hc.is_forking))"
"                                ->  Seq Scan on blocks_reversible hbr  (cost=0.00..1.02 rows=1 width=12) (never executed)"
"                                      Filter: ((num > hc.irreversible_block) AND (fork_id <= hc.fork_id) AND (num <= hc.current_block_num))"
"Planning:"
"  Buffers: shared hit=126"
"Planning Time: 1.371 ms"
"Execution Time: 1.653 ms"

pg17:

"Nested Loop  (cost=52513.25..3462742.24 rows=3012823 width=48) (actual time=854.119..854.202 rows=7 loops=1)"
"  Buffers: shared hit=153188"
"  ->  Limit  (cost=6.32..7.35 rows=1 width=21) (actual time=0.016..0.018 rows=1 loops=1)"
"        Buffers: shared hit=3"
"        ->  Bitmap Heap Scan on contexts hc  (cost=6.32..7.35 rows=1 width=21) (actual time=0.015..0.017 rows=1 loops=1)"
"              Recheck Cond: ((name)::text = 'hafbe_bal'::text)"
"              Heap Blocks: exact=1"
"              Buffers: shared hit=3"
"              ->  Bitmap Index Scan on uq_hive_context_name  (cost=0.00..6.32 rows=1 width=0) (actual time=0.006..0.007 rows=1 loops=1)"
"                    Index Cond: ((name)::text = 'hafbe_bal'::text)"
"                    Buffers: shared hit=2"
"  ->  Append  (cost=52506.92..3410010.48 rows=3012823 width=151) (actual time=854.066..854.115 rows=7 loops=1)"
"        Buffers: shared hit=153185"
"        ->  Subquery Scan on ""*SELECT* 1""  (cost=52506.92..3394923.70 rows=3012822 width=151) (actual time=854.065..854.087 rows=7 loops=1)"
"              Buffers: shared hit=153183"
"              ->  Bitmap Heap Scan on operations ho  (cost=52506.92..3364795.48 rows=3012822 width=157) (actual time=854.064..854.084 rows=7 loops=1)"
"                    Recheck Cond: (((hive.operation_id_to_type_id(id) = ANY ('{40,41,62,32,33,34,59,39,4,20,56,60,52,53,77,70,68}'::integer[])) AND (hive.operation_id_to_block_num(id) <= (LEAST(hc.irreversible_block, hc.current_block_num))) AND (hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279)) OR ((hive.operation_id_to_type_id(id) = 55) AND (hive.operation_id_to_block_num(id) <= (LEAST(hc.irreversible_block, hc.current_block_num))) AND (hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279)) OR ((hive.operation_id_to_type_id(id) = ANY ('{51,63}'::integer[])) AND (hive.operation_id_to_block_num(id) <= (LEAST(hc.irreversible_block, hc.current_block_num))) AND (hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279)))"
"                    Filter: ((hive.operation_id_to_type_id(id) = ANY ('{40,41,62,32,33,34,59,39,4,20,56,60,52,53,77,70,68}'::integer[])) OR ((hive.operation_id_to_type_id(id) = 55) AND (NOT ((((body_binary)::jsonb -> 'value'::text) ->> 'is_saved_into_hbd_balance'::text))::boolean)) OR ((hive.operation_id_to_type_id(id) = ANY ('{51,63}'::integer[])) AND ((((body_binary)::jsonb -> 'value'::text) ->> 'payout_must_be_claimed'::text))::boolean))"
"                    Heap Blocks: exact=1"
"                    Buffers: shared hit=153183"
"                    ->  BitmapOr  (cost=52506.88..52506.88 rows=3112096 width=0) (actual time=854.044..854.044 rows=0 loops=1)"
"                          Buffers: shared hit=153182"
"                          ->  Bitmap Index Scan on hive_operations_op_type_id_block_num  (cost=0.00..47340.71 rows=2932178 width=0) (actual time=853.981..853.981 rows=5 loops=1)"
"                                Index Cond: ((hive.operation_id_to_type_id(id) = ANY ('{40,41,62,32,33,34,59,39,4,20,56,60,52,53,77,70,68}'::integer[])) AND (hive.operation_id_to_block_num(id) <= (LEAST(hc.irreversible_block, hc.current_block_num))) AND (hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279))"
"                                Buffers: shared hit=153170"
"                          ->  Bitmap Index Scan on hive_operations_op_type_id_block_num  (cost=0.00..19.16 rows=1104 width=0) (actual time=0.005..0.005 rows=0 loops=1)"
"                                Index Cond: ((hive.operation_id_to_type_id(id) = 55) AND (hive.operation_id_to_block_num(id) <= (LEAST(hc.irreversible_block, hc.current_block_num))) AND (hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279))"
"                                Buffers: shared hit=4"
"                          ->  Bitmap Index Scan on hive_operations_op_type_id_block_num  (cost=0.00..2887.39 rows=178815 width=0) (actual time=0.053..0.053 rows=2 loops=1)"
"                                Index Cond: ((hive.operation_id_to_type_id(id) = ANY ('{51,63}'::integer[])) AND (hive.operation_id_to_block_num(id) <= (LEAST(hc.irreversible_block, hc.current_block_num))) AND (hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279))"
"                                Buffers: shared hit=8"
"        ->  Subquery Scan on ""*SELECT* 2""  (cost=2.35..22.66 rows=1 width=137) (actual time=0.024..0.025 rows=0 loops=1)"
"              Buffers: shared hit=2"
"              ->  Nested Loop  (cost=2.35..22.65 rows=1 width=143) (actual time=0.023..0.024 rows=0 loops=1)"
"                    Join Filter: ((o.fork_id = (max(hbr.fork_id))) AND (hive.operation_id_to_block_num(o.id) = hbr.num))"
"                    Buffers: shared hit=2"
"                    ->  Index Scan using hive_operations_reversible_block_num_type_id_trx_in_block_fork_ on operations_reversible o  (cost=0.31..20.57 rows=1 width=145) (actual time=0.023..0.023 rows=0 loops=1)"
"                          Index Cond: ((hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279))"
"                          Filter: ((hive.operation_id_to_type_id(id) = ANY ('{40,41,62,32,33,34,59,39,4,20,56,60,52,53,77,70,68}'::integer[])) OR ((hive.operation_id_to_type_id(id) = 55) AND (NOT ((((body_binary)::jsonb -> 'value'::text) ->> 'is_saved_into_hbd_balance'::text))::boolean)) OR ((hive.operation_id_to_type_id(id) = ANY ('{51,63}'::integer[])) AND ((((body_binary)::jsonb -> 'value'::text) ->> 'payout_must_be_claimed'::text))::boolean))"
"                          Buffers: shared hit=2"
"                    ->  GroupAggregate  (cost=2.04..2.06 rows=1 width=12) (never executed)"
"                          Group Key: hbr.num"
"                          ->  Sort  (cost=2.04..2.05 rows=1 width=12) (never executed)"
"                                Sort Key: hbr.num"
"                                ->  Result  (cost=0.00..2.04 rows=1 width=12) (never executed)"
"                                      One-Time Filter: (((hc.current_block_num > hc.irreversible_block) AND hc.is_forking))"
"                                      ->  Seq Scan on blocks_reversible hbr  (cost=0.00..2.04 rows=1 width=12) (never executed)"
"                                            Filter: ((num > hc.irreversible_block) AND (fork_id <= hc.fork_id) AND (num <= hc.current_block_num))"
"Planning:"
"  Buffers: shared hit=117"
"Planning Time: 1.254 ms"
"Execution Time: 854.275 ms"

query plan after changes on pg17:

"CTE Scan on process_block_range_data_b ov  (cost=2067730.46..2223335.31 rows=285849 width=48) (actual time=0.095..0.139 rows=7 loops=1)"
"  Filter: ((op_type_id = ANY ('{40,41,62,32,33,34,59,39,4,20,56,60,52,53,77,70,68}'::integer[])) OR ((op_type_id = 55) AND (NOT (((body -> 'value'::text) ->> 'is_saved_into_hbd_balance'::text))::boolean)) OR ((op_type_id = ANY ('{51,63}'::integer[])) AND (((body -> 'value'::text) ->> 'payout_must_be_claimed'::text))::boolean))"
"  Buffers: shared hit=11"
"  CTE process_block_range_data_b"
"    ->  Nested Loop  (cost=7.03..2067730.42 rows=3112097 width=48) (actual time=0.089..0.129 rows=7 loops=1)"
"          Buffers: shared hit=11"
"          ->  Limit  (cost=6.32..7.35 rows=1 width=21) (actual time=0.028..0.029 rows=1 loops=1)"
"                Buffers: shared hit=3"
"                ->  Bitmap Heap Scan on contexts hc  (cost=6.32..7.35 rows=1 width=21) (actual time=0.028..0.028 rows=1 loops=1)"
"                      Recheck Cond: ((name)::text = 'hafbe_bal'::text)"
"                      Heap Blocks: exact=1"
"                      Buffers: shared hit=3"
"                      ->  Bitmap Index Scan on uq_hive_context_name  (cost=0.00..6.32 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=1)"
"                            Index Cond: ((name)::text = 'hafbe_bal'::text)"
"                            Buffers: shared hit=2"
"          ->  Append  (cost=0.71..2013261.36 rows=3112097 width=151) (actual time=0.021..0.033 rows=7 loops=1)"
"                Buffers: shared hit=8"
"                ->  Subquery Scan on ""*SELECT* 1""  (cost=0.71..1997681.32 rows=3112096 width=151) (actual time=0.020..0.023 rows=7 loops=1)"
"                      Buffers: shared hit=6"
"                      ->  Index Scan using hive_operations_block_num_trx_in_block_idx on operations ho  (cost=0.71..1966560.36 rows=3112096 width=157) (actual time=0.019..0.021 rows=7 loops=1)"
"                            Index Cond: ((hive.operation_id_to_block_num(id) <= (LEAST(hc.irreversible_block, hc.current_block_num))) AND (hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279) AND (hive.operation_id_to_type_id(id) = ANY ('{40,41,62,32,33,34,59,39,4,20,56,60,52,53,77,70,68,51,63,55}'::integer[])))"
"                            Buffers: shared hit=6"
"                ->  Subquery Scan on ""*SELECT* 2""  (cost=2.31..19.55 rows=1 width=188) (actual time=0.008..0.008 rows=0 loops=1)"
"                      Buffers: shared hit=2"
"                      ->  Nested Loop  (cost=2.31..19.54 rows=1 width=194) (actual time=0.008..0.008 rows=0 loops=1)"
"                            Join Filter: ((o.fork_id = (max(hbr.fork_id))) AND (hive.operation_id_to_block_num(o.id) = hbr.num))"
"                            Buffers: shared hit=2"
"                            ->  Index Scan using hive_operations_reversible_block_num_type_id_trx_in_block_fork_ on operations_reversible o  (cost=0.27..17.46 rows=1 width=196) (actual time=0.008..0.008 rows=0 loops=1)"
"                                  Index Cond: ((hive.operation_id_to_block_num(id) >= 90833279) AND (hive.operation_id_to_block_num(id) <= 90833279) AND (hive.operation_id_to_type_id(id) = ANY ('{40,41,62,32,33,34,59,39,4,20,56,60,52,53,77,70,68,51,63,55}'::integer[])))"
"                                  Buffers: shared hit=2"
"                            ->  GroupAggregate  (cost=2.04..2.06 rows=1 width=12) (never executed)"
"                                  Group Key: hbr.num"
"                                  ->  Sort  (cost=2.04..2.05 rows=1 width=12) (never executed)"
"                                        Sort Key: hbr.num"
"                                        ->  Result  (cost=0.00..2.04 rows=1 width=12) (never executed)"
"                                              One-Time Filter: (((hc.current_block_num > hc.irreversible_block) AND hc.is_forking))"
"                                              ->  Seq Scan on blocks_reversible hbr  (cost=0.00..2.04 rows=1 width=12) (never executed)"
"                                                    Filter: ((num > hc.irreversible_block) AND (fork_id <= hc.fork_id) AND (num <= hc.current_block_num))"
"Planning:"
"  Buffers: shared hit=102"
"Planning Time: 0.755 ms"
"Execution Time: 0.187 ms"