我有一个大表,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);
慢速计划仅在 上使用索引
(block_number)
,并过滤 1100 万行。这是昂贵的废话,并且在存在更合适的索引时不应该发生(chain_id, block_number)
。chain_id = 1
除非你有统计数据使 Postgres(错误地)期望它会很快在最新的行中找到 10 行。快速计划幸运的是,最新的 10 行(最大的
block_number
)恰好有chain_id = 2
. 任何其他人的chain_id
表现都会更差。假设最大的行
block_number
是最近的条目,这些通常不会反映在列统计信息中。Postgres 从以前的统计数据中做出猜测。所以:
您确实说过多列索引吗?最好 on
(chain_id, block_number DESC)
,但如果block_number
已定义NOT NULL
,DESC
则无关紧要。运行
ANALYZE extrinsics;
。然后再测试。问题消失了吗?可能的解决方案
部分索引
如果只有几个不同的
chain_id
,我会为每个(感兴趣的)创建一个单独的部分索引。这应该说服 Postgres,并且较小的索引应该更快:每行 8 个字节的有效负载而不是 16 个(带填充)。所以:单独的表/分区
如果只有一个小的固定数字,并且查询总是只涉及单个
chain_id
,请考虑为每个链开始一个单独的表,或者列表分区。索引和查询会一致。自相矛盾的干预
这类糟糕的查询计划的主要问题是 Postgres 认为它可以只遍历一个简单的索引并快速过滤足够多的行以满足非常小的
LIMIT
. 有多种方法可以让 Postgres 重新考虑:改进列统计信息,ANALYZE
强制执行某些统计信息,例如n_distinct
,计划程序常量,例如random_page_cost
,删除膨胀,VACUUM
更多 RAM,......如果在任何情况下出现问题,您都应该直接设置所有这些。但是
LIMIT
只有很少的不同的非常小的chain_id
查询计划对于这个简单的查询计划来说非常诱人。“蛮力”解决方法是将 增加到LIMIT
刚好足以使天平倾斜。在某些时候,这种方法的估计成本可能会比其他一些(实际上更好的)计划更大。对于另一个计划,一个LIMIT 100
代替可能只意味着多几毫秒。LIMIT 10
所以:DESC NULLS LAST
与ASC
索引配合得很好。所以这不会有什么坏处,即使无论如何block_number
都定义了NOT NULL
。(Postgres 并不总是考虑到这一点。)参见:实验调试
因为你有两个 -如果(!)你可以自由地这样做 - 将索引放在 just 上
(block_number)
并再次测试慢速查询,同时确保存在所述多列索引。好指数现在回升了吗?说什么
EXPLAIN
?由于删除和重新创建一个巨大的索引是破坏性的、阻塞的并且代价高昂,你可能会以超级用户的身份使用 hack 来破坏系统目录。
免责声明:你需要知道你在做什么,否则你可能会破坏你的数据库(集群)。将其包装在事务中并回滚。这使你不太可能破坏任何东西。
应该很快。
如果你犯错了,要恢复:
虽然我们无论如何都会回滚,但我们可能只使用
DROP INDEX
. 那只是痛苦,我们没有。背景,引用手册
pg_index
:并且,关于“同时建立索引”的手册
CREATE INDEX
:上面的 hack 利用它来暂时“禁用”索引。如前所述,索引始终保持不变,因此即使在并发写入负载下,没有排他锁,也不会出现任何问题。我曾多次使用此技巧。弄乱系统目录根本就不是“安全”的。
在旁边
重新排列列通常会节省一些存储空间和性能:
看: