我有一个我想更好地理解的情况:
我有一个带有两个行和一个索引的表t
:
CREATE TABLE t (
refid BIGINT NOT NULL,
created TIMESTAMPTZ NOT NULL
);
CREATE INDEX t_refid_created ON t (refid, created);
为了获取每个不同refid
的最新(created
值最高)行,我撰写了两个查询:
-- index only scan t_refid_created_desc_idx
SELECT DISTINCT ON (refid) * FROM t
ORDER BY refid, created DESC;
-- index scan t_refid_created_idx
SELECT refid, max(created) FROM t GROUP BY refid;
当t
的行约为16m,refid
的差异约为500个不同的值时,第二个查询返回的速度大大快于第二个。
首先,我想到,因为我通过created DESC
订购,因此需要进行后退索引扫描,并且从具有较高差异的值开始(创建)。所以我添加了以下索引:
CREATE index t_refid_created_desc_idx ON t (refid, created DESC);
确实使用了(而不是上一个索引上的向后扫描),但没有改进。
如果我正确理解,则第二个查询将通过refid
汇总,然后扫描每个聚合以找到最大created
值。这听起来像是很多工作。就我的理解而言,第一个查询应该简单地在索引的第一部分中进行迭代,然后对于每个refid
,它应该使用索引的第二部分,占据了第一个值。
显然不是这种情况,SELECT DISTINCT
查询需要GROUP BY
的两倍。
我在这里缺少什么?
这是第一个和第二个查询的EXPLAIN ANALYZE
输出:
Unique (cost=0.56..850119.78 rows=291 width=16) (actual time=0.103..13414.913 rows=469 loops=1)
-> Index Only Scan using t_refid_created_desc_idx on t (cost=0.56..808518.47 rows=16640527 width=16) (actual time=0.102..12113.454 rows=16640527 loops=1)
Heap Fetches: 16640527
Planning time: 0.157 ms
Execution time: 13415.047 ms
Finalize GroupAggregate (cost=599925.13..599932.41 rows=291 width=16) (actual time=3454.350..3454.884 rows=469 loops=1)
Group Key: refid
-> Sort (cost=599925.13..599926.59 rows=582 width=16) (actual time=3454.344..3454.509 rows=1372 loops=1)
Sort Key: refid
Sort Method: quicksort Memory: 113kB
-> Gather (cost=599837.29..599898.40 rows=582 width=16) (actual time=3453.194..3560.602 rows=1372 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial HashAggregate (cost=598837.29..598840.20 rows=291 width=16) (actual time=3448.225..3448.357 rows=457 loops=3)
Group Key: refid
-> Parallel Seq Scan on t (cost=0.00..564169.53 rows=6933553 width=16) (actual time=0.047..2164.459 rows=5546842 loops=3)
Planning time: 0.157 ms
Execution time: 3561.727 ms
第一个查询在大约10秒内运行,而第二个查询在2秒内取得了相同的结果!甚至不使用索引!
我正在使用postgresql 10.5。
我无法回答为什么DISTINCT ON
不考虑第二个计划。从成本估算中,我们可以看到PostgreSQL认为它更便宜。
我想没有人实施将DISTINCT
推向并行计划。您可以询问邮件列表。
但是,第一个查询的问题是提取1600万。这意味着这实际上是普通的索引扫描!
看起来像是一个不好的误会。如果我是对的,则表上清洁可见性图的VACUUM
应大大改进第一个查询。