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

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

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

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

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

Комментарии (5)


  1. nikolai-averin
    31.01.2022 23:32
    +3

    У вас результаты исходного и оптимизированного запросов отличаются. Исходный запрос сперва делает join, а потом sum, а второй сперва sum, а потом DISTINCT, из-за чего сумма в 8 раз больше. До:

    i	sum
    1	288
    2	288
    3	288

    После:

    i	sum
    1	36
    2	36
    3	36


    1. Kilor Автор
      31.01.2022 23:43

      Совершенно верно, и это еще один повод проверять каждую гипотезу на фактических данных.

      В реальных условиях при подобных модификациях запроса может (и наверняка будет) меняться агрегатная функция для получения совпадающего результата - например, sum превратится в count или возникнет необходимость добавить FILTER.


  1. mixsture
    01.02.2022 14:34

    Ну 3 метод существенно меняет логику запроса, поэтому обычно неприменим. Первый метод сводится к правилу «не надо выбирать лишних данных», ну точнее очень близко к нему лежит по смыслу: «не надо соединяться с таблицей, из которой данные не нужны» — и его тоже мало шансов встретить на реальном проекте.

    Из моей практики: проблемы обычно возникают со сложными аналитическими запросами, т.к. они пытаются собрать данные из кучи таблиц, что-то там просуммировать и все это с хорошей вложенностью, что путает планировщик, а еще требует кучи индексов по всем этим таблицам. Так вот для таких случаев в 99% работает способ денормализации бд: т.е. мы делаем отдельную таблицу в бд «удобную для этого запроса», куда, дублируя данные, пишем на операциях записи.


    1. Kilor Автор
      01.02.2022 14:47

      Первый метод сводится к правилу «не надо выбирать лишних данных», ну точнее очень близко к нему лежит по смыслу: «не надо соединяться с таблицей, из которой данные не нужны» — и его тоже мало шансов встретить на реальном проекте.

      Вот как раз с попытками "сначала все приджойнить" в реальном проекте приходится сталкиваться очень часто - даже разбирал на примерах.


    1. FruTb
      01.02.2022 17:37

      Мне кажется что все же сложные аналитические выборки лучше не в OLTP базе делать а в OLAP-кубе. И если вы уже как часть практики имеете денормализованные таблицы - наверное уже пповод подумать и подключить OLAP