The initial index on msg.data->'@type' added as part of #56 wasn't actually used by the query in all_ecocredit_txes, which relies on LIKE operator.
By using instead another type of index based on gin_trgm_ops operator class from pg_trgm extension (support for similarity of text using trigram matching), the query is much faster (testing with a large db as on prod):
postgres=> explain analyze select * from all_ecocredit_txes();
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=68667.26..68684.84 rows=7032 width=335) (actual time=42.309..42.443 rows=2138 loops=1)
Sort Key: tx.block_height DESC
Sort Method: quicksort Memory: 399kB
-> Nested Loop (cost=1301.55..68217.92 rows=7032 width=335) (actual time=2.085..41.268 rows=2138 loops=1)
-> Bitmap Heap Scan on msg (cost=1301.13..22096.12 rows=7371 width=12) (actual time=1.369..3.535 rows=2364 loops=1)
Recheck Cond: ((data ->> '@type'::text) ~~ '/regen.ecocredit.%'::text)
Heap Blocks: exact=1463
-> Bitmap Index Scan on msg_data_type_gin_idx (cost=0.00..1299.29 rows=7371 width=0) (actual time=1.217..1.218 rows=2364 loops=1)
Index Cond: ((data ->> '@type'::text) ~~ '/regen.ecocredit.%'::text)
-> Index Scan using tx_pkey on tx (cost=0.42..6.26 rows=1 width=335) (actual time=0.016..0.016 rows=1 loops=2364)
Index Cond: ((chain_num = msg.chain_num) AND (block_height = msg.block_height) AND (tx_idx = msg.tx_idx))
Filter: (((data -> 'tx_response'::text) -> 'code'::text) = '0'::jsonb)
Rows Removed by Filter: 0
Planning Time: 0.493 ms
Execution Time: 42.556 ms
closes: #55
The initial index on
msg.data->'@type'
added as part of #56 wasn't actually used by the query inall_ecocredit_txes
, which relies onLIKE
operator. By using instead another type of index based ongin_trgm_ops
operator class frompg_trgm
extension (support for similarity of text using trigram matching), the query is much faster (testing with a large db as on prod):