Примитивный запрос - простой джойн и группировка. Традиционные методы оптимизации - казалось бы, что могло пойти не так?..
Небольшой эксперимент, на тему необходимости проверки любых гипотез в конкретных условиях.
Возьмем исходный запрос:
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)
mixsture
01.02.2022 14:34Ну 3 метод существенно меняет логику запроса, поэтому обычно неприменим. Первый метод сводится к правилу «не надо выбирать лишних данных», ну точнее очень близко к нему лежит по смыслу: «не надо соединяться с таблицей, из которой данные не нужны» — и его тоже мало шансов встретить на реальном проекте.
Из моей практики: проблемы обычно возникают со сложными аналитическими запросами, т.к. они пытаются собрать данные из кучи таблиц, что-то там просуммировать и все это с хорошей вложенностью, что путает планировщик, а еще требует кучи индексов по всем этим таблицам. Так вот для таких случаев в 99% работает способ денормализации бд: т.е. мы делаем отдельную таблицу в бд «удобную для этого запроса», куда, дублируя данные, пишем на операциях записи.Kilor Автор
01.02.2022 14:47Первый метод сводится к правилу «не надо выбирать лишних данных», ну точнее очень близко к нему лежит по смыслу: «не надо соединяться с таблицей, из которой данные не нужны» — и его тоже мало шансов встретить на реальном проекте.
Вот как раз с попытками "сначала все приджойнить" в реальном проекте приходится сталкиваться очень часто - даже разбирал на примерах.
FruTb
01.02.2022 17:37Мне кажется что все же сложные аналитические выборки лучше не в OLTP базе делать а в OLAP-кубе. И если вы уже как часть практики имеете денормализованные таблицы - наверное уже пповод подумать и подключить OLAP
nikolai-averin
У вас результаты исходного и оптимизированного запросов отличаются. Исходный запрос сперва делает join, а потом sum, а второй сперва sum, а потом DISTINCT, из-за чего сумма в 8 раз больше. До:
После:
Kilor Автор
Совершенно верно, и это еще один повод проверять каждую гипотезу на фактических данных.
В реальных условиях при подобных модификациях запроса может (и наверняка будет) меняться агрегатная функция для получения совпадающего результата - например, sum превратится в count или возникнет необходимость добавить FILTER.