Примитивный запрос — простой джойн и группировка. Традиционные методы оптимизации — казалось бы, что могло пойти не так?..

Небольшой эксперимент, на тему необходимости проверки любых гипотез в конкретных условиях.

Возьмем исходный запрос:

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

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