我有一个大表,extrinsics
大小将近 90GB,包含来自多个区块链的数据。
我有一个需要将近 17 分钟才能运行的查询:
select * from public.extrinsics
where chain_id = 1
ORDER BY "extrinsics"."block_number" DESC
limit 10;
我可以翻转chain_id
并2
运行查询,运行时间不到一秒钟。
select count(*) from "extrinsics" where chain_id = 1
= ~ 38M 行
select count(*) from "extrinsics" where chain_id = 2
= ~ 58M 行
我试过(没有运气):
- 在 chain_id 和 block_number 上创建多列索引。
- 在 chain_id 和 block_number order DESC 上创建多列索引。
- 将 block_number 的统计数据增加到 10,000 并运行 ANALYZE
- 将 chain_id 的统计数据增加到 10,000 并运行 ANALYZE
起初我以为是查询计划,即使我有上面提到的其他索引,它总是求助于向后扫描我的索引,但如果2 仍然很快,block_number
查询计划错误似乎不是问题。chain_id
要求解释:
链 2(快速):
"Limit (cost=0.57..7.76 rows=10 width=829) (actual time=1.563..2.379 rows=10 loops=1)"
" Buffers: shared read=9"
" I/O Timings: read=2.310"
" -> Index Scan Backward using index_extrinsics_on_block_number on extrinsics (cost=0.57..41768857.19 rows=58091433 width=829) (actual time=1.561..2.375 rows=10 loops=1)"
" Filter: (chain_id = 2)"
" Buffers: shared read=9"
" I/O Timings: read=2.310"
"Planning Time: 0.636 ms"
"Execution Time: 2.417 ms"
链 1(慢):
"Limit (cost=0.57..11.60 rows=10 width=829) (actual time=912353.888..912356.009 rows=10 loops=1)"
" Buffers: shared hit=1872576 read=2079934"
" I/O Timings: read=890705.882"
" -> Index Scan Backward using index_extrinsics_on_block_number on extrinsics (cost=0.57..41768857.19 rows=37874906 width=829) (actual time=912353.886..912356.003 rows=10 loops=1)"
" Filter: (chain_id = 1)"
" Rows Removed by Filter: 10936113"
" Buffers: shared hit=1872576 read=2079934"
" I/O Timings: read=890705.882"
"Planning Time: 0.207 ms"
"Execution Time: 912356.134 ms"
-- Table Definition ----------------------------------------------
CREATE TABLE extrinsics (
id BIGSERIAL PRIMARY KEY,
block_number bigint NOT NULL,
extrinsic_index integer,
timestamp bigint,
is_signed boolean DEFAULT false,
signer character varying,
method character varying,
section character varying,
args jsonb,
extrinsic_hash character varying,
doc character varying[],
success boolean DEFAULT false,
created_at timestamp(6) without time zone NOT NULL,
updated_at timestamp(6) without time zone NOT NULL,
chain_id integer NOT NULL DEFAULT 1
);
-- Indices -------------------------------------------------------
CREATE UNIQUE INDEX extrinsics_pkey ON extrinsics(id int8_ops);
CREATE INDEX index_extrinsics_on_block_number ON extrinsics(block_number int8_ops);
CREATE INDEX index_extrinsics_on_chain_id ON extrinsics(chain_id int4_ops);
CREATE INDEX index_extrinsics_on_signer ON extrinsics(signer text_ops);
CREATE INDEX index_extrinsics_on_signer_and_chain_id ON extrinsics(signer text_ops,chain_id int4_ops);
CREATE UNIQUE INDEX uniq_extrinsics ON extrinsics(block_number int8_ops,extrinsic_index int4_ops,chain_id int4_ops);
CREATE INDEX index_extrinsics_on_chain_id_and_block_number ON extrinsics(chain_id int4_ops,block_number int8_ops);
CREATE INDEX blocks_front_page_index ON extrinsics(chain_id int4_ops,block_number int8_ops DESC);
CREATE INDEX dee_test ON extrinsics(block_number int8_ops DESC NULLS LAST);