PostgreSQL Antipatterns: делаем группировку быстрее от 0.1 до 5 раз
Примитивный запрос — простой джойн и группировка. Традиционные методы оптимизации — казалось бы, что могло пойти не так?…
Небольшой эксперимент, на тему необходимости проверки любых гипотез в конкретных условиях.
Возьмем исходный запрос:
WITH vals AS (
SELECT
i
, unnest('{1,2,3,4,5,6,7,8}'::integer[]) v
FROM
generate_series(1, 10000) i
)
SELECT
v2.i
, sum(v1.v)
FROM
vals v1
JOIN
vals v2
USING(i)
GROUP BY
1;
294ms — это будет наше стартовое время, которое мы попробуем ускорить. Ну и 640K записей, которые пришлось обработать в Merge Join.
Внимание на ключи группировки!
У нас с запросе используется USING(i) — то есть ON v1.i = v2.i, а потом — GROUP BY 1 — группировка по первому полю результата, которым в нашем случае является v2.i.
То есть происходит группировка по полю связанной таблицы, а сама агрегация — по данным основной таблицы! Не надо так. Этим вы отсекаете планировщику возможность рассмотреть вариант соединения таблиц уже после группировки.
Исправим эту досадную помарку. Но в нашем примере для CTE это не влияет.
Зачем нам соединение таблиц?
Раз мы выяснили, что вся группировка может быть получена уже по первой таблице, то [INNER] JOIN можно заменить проверкой наличия такого значения в таблице «справа»:
WITH vals AS (...)
SELECT
i
, sum(v)
FROM
vals
WHERE
i IN (SELECT DISTINCT i FROM vals)
GROUP BY
1;
Всего 85ms и Merge Join заменился на Hash Join, выдающий всего 80K записей.
Не все массивы одинаково полезны
Буквально, «на автомате» исправляем IN (...) на = ANY(ARRAY(...)), ведь это эффективно предотвращает возможное «разворачивание» в соединение обычного сканирования таблицы с константным условием:
WITH vals AS (...)
SELECT
i
, sum(v)
FROM
vals
WHERE
i = ANY(ARRAY(SELECT DISTINCT i FROM vals))
GROUP BY
1;
И… грабли больно бьют нас по лбу: 2609ms — почти в 10 раз хуже первоначального времени! А все потому, что проверить 80K раз наличие элемента в массиве на 10K элементов — ни разу не дешево, и такую технику оптимизации можно использовать только при достаточно «коротких» массивах.
GROUP (JOIN) vs JOIN (GROUP)
Но у нас по-прежнему условия соединения проверяются для 80K записей, а «на выход» отдается всего 10K — как бы их сократить?… Для этого внесем группировку «под скобки»:
WITH vals AS (...)
SELECT
*
FROM
(
SELECT
i
, sum(v)
FROM
vals
GROUP BY
1
) grp
WHERE
i IN (SELECT DISTINCT i FROM vals);
Итого: 68ms, или в 4.5 раз быстрее оригинала.
Понятно, что если мы обратим внимание на сам источник данных, то и множественные обращения к нему можно было бы сократить.
