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

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

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

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

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