PostgreSQL: последняя строка в DISTINCT ON менее производительная, чем строка max в GROUP BY

Question

У меня есть ситуация, которую я хотел бы лучше понять:

У меня есть стол t с двумя строками и одним индексом:

CREATE TABLE t (
  refid                 BIGINT NOT NULL,
  created               TIMESTAMPTZ NOT NULL
);
CREATE INDEX t_refid_created ON t (refid, created);

Для того, чтобы получить последние (с самым высоким created значение) строка для каждого отдельного 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 имеет около 16 миллионов строк и дисперсию в 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 в параллельные планы. Вы можете спросить список рассылки.

Однако проблема с первым запросом - 16 миллионов выборок кучи. Это означает, что это на самом деле нормальное сканирование индекса! Похоже, плохая недооценка на стороне планировщика.

Если я прав, VACUUM на таблице, которая очищает карту видимости, должна значительно улучшить первый запрос.