postgresql - PostgreSQL:DISTINCT 中的最新行的性能低于 GROUP BY 中的最大行
问题描述
我有一种情况,我想更好地理解:
我有一个t
有两行和一个索引的表:
CREATE TABLE t (
refid BIGINT NOT NULL,
created TIMESTAMPTZ NOT NULL
);
CREATE INDEX t_refid_created ON t (refid, created);
为了获得created
每个 distinct 的最新(最高值)行refid
,我编写了两个查询:
-- 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
的,清理可见性地图的表上的 a应该会大大改善第一个查询。
推荐阅读
- jenkins - 哪种 ANT 风格的路径模式可以在 JenkinsFile 中捕获 foo_1.23.456_bar.baz 但不能捕获 foo_1.23.456_qux_bar.baz?
- java - 在java中打印2个日期之间的所有日期,特殊情况
- hyperledger - 运行超级账本 iroha 的快速启动版本和构建 iroha 有什么区别?
- azure-api-management - Azure - API 管理 - 管理 API SAS 令牌到期
- linear-programming - In Scipy Linprog, is there a setting that makes the results "nicer"?
- c++ - 我可以将 CFMutableDictionaryRef 转换为 CFDictionaryRef 吗?
- javascript - 使用 jquery 或 js 刷新所有浏览器选项卡
- android - 如何以编程方式从 Firebase 获取无崩溃用户的百分比
- django - 如何在 Azure Kubernetes 服务上向公众隐藏 Django 管理员,同时通过后门保持访问
- c# - 在 Asp.net 核心 Web API 中使用 Continuation 令牌的正确方法是什么