![](https://habrastorage.org/getpro/habr/upload_files/a68/fcb/783/a68fcb783c41ad33c990c65ce764b55e.jpeg)
Примитивный запрос — простой джойн и группировка. Традиционные методы оптимизации — казалось бы, что могло пойти не так?..
Небольшой эксперимент, на тему необходимости проверки любых гипотез в конкретных условиях.
Возьмем исходный запрос:
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;
![](https://habrastorage.org/getpro/habr/upload_files/441/ff9/eae/441ff9eae932952cc0e64dddf4c114f9.png)
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;
![](https://habrastorage.org/getpro/habr/upload_files/843/e6f/c32/843e6fc3289e89d44ed0c6917e688753.png)
Всего 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;
![](https://habrastorage.org/getpro/habr/upload_files/a5f/710/4c6/a5f7104c689655a99d71c6ff93007114.png)
И… грабли больно бьют нас по лбу: 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);
![](https://habrastorage.org/getpro/habr/upload_files/7d0/72e/5b6/7d072e5b65f5c3d161222a6f96d3c873.png)
Итого: 68ms, или в 4.5 раз быстрее оригинала.
Понятно, что если мы обратим внимание на сам источник данных, то и множественные обращения к нему можно было бы сократить.
ссылка на оригинал статьи https://habr.com/ru/company/tensor/blog/648863/
Добавить комментарий