Draft: Unify blocksearch index
QUERY:
explain analyze
WITH operation_range AS (
SELECT DISTINCT ov.block_num FROM hive.operations_view ov
WHERE
ov.op_type_id = 1
AND ov.block_num BETWEEN 84900000 AND 85000000
AND jsonb_extract_path_text(ov.body, 'value', 'author') = 'blocktrades'
ORDER BY ov.block_num desc
LIMIT 100)
SELECT opr.block_num, ARRAY(SELECT 1::smallint) FROM operation_range opr
ORDER BY opr.block_num desc;STEEM11 (one index for blocksearch operations) added block_num to eliminate BITMAP AND with block_num index on hive.operations
CREATE INDEX IF NOT EXISTS hive_operations_block_search_permlink_author ON hive.operations USING gin
(
jsonb_extract_path_text(body_binary::jsonb, VARIADIC ARRAY['value', 'author'])
jsonb_extract_path_text(body_binary::jsonb, VARIADIC ARRAY['value', 'permlink']) ,
block_num
)
WHERE op_type_id = ANY (ARRAY[0, 1, 17, 19, 51, 52, 53, 61, 63, 72, 73]); Subquery Scan on opr (cost=148893.31..148911.14 rows=100 width=36) (actual time=48860.979..48860.988 rows=14 loops=1)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=2) (actual time=0.002..0.003 rows=1 loops=1)
-> Limit (cost=148893.30..148911.13 rows=100 width=4) (actual time=48860.968..48860.975 rows=14 loops=1)
-> Unique (cost=148893.30..148928.95 rows=200 width=4) (actual time=48850.476..48850.482 rows=14 loops=1)
-> Sort (cost=148893.30..148911.13 rows=7130 width=4) (actual time=48850.475..48850.477 rows=14 loops=1)
Sort Key: ho.block_num DESC
Sort Method: quicksort Memory: 25kB
-> Append (cost=1363.13..148437.00 rows=7130 width=4) (actual time=48849.353..48850.458 rows=14 loops=1)
-> Bitmap Heap Scan on operations ho (cost=1363.13..148385.85 rows=7129 width=4) (actual time=48849.352..48850.328 rows=14 loops=1)
Recheck Cond: ((jsonb_extract_path_text((body_binary)::jsonb, VARIADIC '{value,author}'::text[]) = 'blocktrades'::text) AND (block_num >= 84000000) AND (block_num <= 85000000) AND (op_type_id = ANY ('{0,1,17,19,51,52,53,61,63,72,73}'::integer[])))
Filter: (op_type_id = 1)
Rows Removed by Filter: 2826
Heap Blocks: exact=705
-> Bitmap Index Scan on hive_operations_block_search_permlink_author (cost=0.00..1361.35 rows=142165 width=0) (actual time=48849.218..48849.218 rows=2840 loops=1)
Index Cond: ((jsonb_extract_path_text((body_binary)::jsonb, VARIADIC '{value,author}'::text[]) = 'blocktrades'::text) AND (block_num >= 84000000) AND (block_num <= 85000000))
-> Subquery Scan on "*SELECT* 2" (cost=3.30..15.49 rows=1 width=4) (actual time=0.126..0.127 rows=0 loops=1)
-> Nested Loop (cost=3.30..15.48 rows=1 width=92) (actual time=0.124..0.126 rows=0 loops=1)
Join Filter: ((o.block_num = hbr.num) AND (o.fork_id = (max(hbr.fork_id))))
-> Index Scan using hive_operations_reversible_block_num_type_id_trx_in_block_fork_ on operations_reversible o (cost=0.27..12.41 rows=1 width=12) (actual time=0.124..0.124 rows=0 loops=1)
Index Cond: ((block_num >= 84000000) AND (block_num <= 85000000) AND (op_type_id = 1))
Filter: (jsonb_extract_path_text((body_binary)::jsonb, VARIADIC '{value,author}'::text[]) = 'blocktrades'::text)
-> GroupAggregate (cost=3.03..3.05 rows=1 width=12) (never executed)
Group Key: hbr.num
InitPlan 2 (returns $1)
-> Seq Scan on irreversible_data hid (cost=0.00..1.01 rows=1 width=4) (never executed)
-> Sort (cost=2.02..2.03 rows=1 width=12) (never executed)
Sort Key: hbr.num
-> Seq Scan on blocks_reversible hbr (cost=0.00..2.01 rows=1 width=12) (never executed)
Filter: (num > $1)
Planning Time: 6.677 ms
JIT:
Functions: 31
Options: Inlining false, Optimization false, Expressions true, Deforming true
Timing: Generation 0.846 ms, Inlining 0.000 ms, Optimization 0.435 ms, Emission 10.081 ms, Total 11.362 ms
Execution Time: 49007.960 msHIVE-3 (index for each operation)
"Subquery Scan on opr (cost=460.86..73006.28 rows=100 width=36) (actual time=2358.222..2358.225 rows=0 loops=1)"
" InitPlan 1 (returns $0)"
" -> Result (cost=0.00..0.01 rows=1 width=2) (never executed)"
" -> Limit (cost=460.85..73006.27 rows=100 width=4) (actual time=2358.221..2358.223 rows=0 loops=1)"
" -> Unique (cost=460.85..145551.69 rows=200 width=4) (actual time=2358.220..2358.222 rows=0 loops=1)"
" -> Merge Append (cost=460.85..145549.92 rows=710 width=4) (actual time=2358.220..2358.221 rows=0 loops=1)"
" Sort Key: ho.block_num DESC"
" -> Index Scan Backward using hive_operations_op_type_id_block_num on operations ho (cost=0.58..145082.55 rows=709 width=4) (actual time=2358.195..2358.196 rows=0 loops=1)"
" Index Cond: ((op_type_id = 1) AND (block_num >= 84900000) AND (block_num <= 85000000))"
" Filter: (jsonb_extract_path_text((body_binary)::jsonb, VARIADIC '{value,author}'::text[]) = 'blocktrades'::text)"
" Rows Removed by Filter: 92143"
" -> Sort (cost=460.25..460.26 rows=1 width=4) (actual time=0.023..0.024 rows=0 loops=1)"
" Sort Key: ""*SELECT* 2"".block_num DESC"
" Sort Method: quicksort Memory: 25kB"
" -> Subquery Scan on ""*SELECT* 2"" (cost=459.16..460.24 rows=1 width=4) (actual time=0.018..0.020 rows=0 loops=1)"
" -> Nested Loop (cost=459.16..460.23 rows=1 width=92) (actual time=0.018..0.019 rows=0 loops=1)"
" -> GroupAggregate (cost=458.02..458.04 rows=1 width=12) (actual time=0.017..0.018 rows=0 loops=1)"
" Group Key: hbr.num"
" InitPlan 2 (returns $1)"
" -> Seq Scan on irreversible_data hid (cost=0.00..457.01 rows=1 width=4) (never executed)"
" -> Sort (cost=1.01..1.01 rows=1 width=12) (actual time=0.017..0.017 rows=0 loops=1)"
" Sort Key: hbr.num"
" Sort Method: quicksort Memory: 25kB"
" -> Seq Scan on blocks_reversible hbr (cost=0.00..1.00 rows=1 width=12) (actual time=0.014..0.015 rows=0 loops=1)"
" Filter: (num > $1)"
" -> Bitmap Heap Scan on operations_reversible o (cost=1.14..2.18 rows=1 width=12) (never executed)"
" Recheck Cond: (fork_id = (max(hbr.fork_id)))"
" Filter: ((block_num >= 84900000) AND (block_num <= 85000000) AND (op_type_id = 1) AND (block_num = hbr.num) AND (jsonb_extract_path_text((body_binary)::jsonb, VARIADIC '{value,author}'::text[]) = 'blocktrades'::text))"
" -> Bitmap Index Scan on uq_operations_reversible (cost=0.00..1.14 rows=1 width=0) (never executed)"
" Index Cond: (fork_id = (max(hbr.fork_id)))"
"Planning Time: 9.441 ms"
"Execution Time: 2360.570 ms"