PostgreSQL:DISTINCT 中的最新行性能低于 GROUP BY 中的最大行

Question

我想更好地了解一种情况:

我有一个表 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 有大约 1600 万行并且 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。

Answer 1

我无法回答为什么 DISTINCT ON 不考虑第二个计划的谜语。从成本估算中我们看到 PostgreSQL 认为它更便宜。

我猜想没有人将 DISTINCT 下推到并行计划中。你可以问邮件列表。

但是，第一个查询的问题是 1600 万次堆提取。这意味着这实际上是一个普通的索引扫描!这看起来像是计划者方面的错误估计。

如果我是对的，清理可见性 map 的表上的 VACUUM 应该会显着改善第一个查询。