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 раз быстрее оригинала.

Понятно, что если мы обратим внимание на сам источник данных, то и множественные обращения к нему можно было бы сократить.

Читайте так же:

  • [Перевод] Сборка собственного RPM-пакета, содержащего простую Go-программу[Перевод] Сборка собственного RPM-пакета, содержащего простую Go-программу Процесс развёртывания программ обычно состоит из множества этапов. Некоторые из этих этапов могут представлять собой довольно-таки сложные последовательности действий. В наши дни имеется широкое разнообразие инструментов, позволяющих создавать описания процессов развёртывания программ, […]
  • Как мы продвигали опалубки, а продвинули бытовки: SEO-кейс Kokoc.comКак мы продвигали опалубки, а продвинули бытовки: SEO-кейс Kokoc.com О трудовых подвигах сеошников не слагают былин и баллад. Даже когда они совершают невозможное, их достижения остаются незаметными для большинства. Сегодня Екатерина Чекалина, аккаунт Kokoc.com. Спасает от забвения вдохновляющую историю про упорство. Веру в себя и вывод в ТОП-3 сайта по […]
  • Редкая вещь – калькулятор TI для финансового инженераРедкая вещь – калькулятор TI для финансового инженера Изображение с сайта виртуального музея www.datamath.orgКак бы вы отреагировали, если бы увидели на панели калькулятора кнопки: в первом ряду - CAL и PUT, а в следующем - BUY и SEL? Если вы хотя бы немного знакомы с финансами и инвестициями, то, скорее всего, подумали бы (про себя): ну, […]
  • НАСА незаметно финансирует охоту на инопланетные мегаструктурыНАСА незаметно финансирует охоту на инопланетные мегаструктуры За последнее десятилетие мы изобрели удивительные инструменты — обсерватории и орбитальные телескопы — позволяющие внимательно смотреть на звезды. И обнаружили, что вокруг них часто можно увидеть планеты. Теперь ученые пытаются выяснить, может ли в таких мирах существовать жизнь. Есть […]