Как предварительная агрегация избавляет от взрыва строк

Переработав очередной алерт о долгом запросе, Макс задумался.

— Лене стоило бы это увидеть, — пробормотал он, подключаясь к тестовой базе.

Через 10 минут был готов демо-стенд для объяснения утренней проблемы. На экране красовались два одинаковых по смыслу запроса. Один — медленный, как сонный слак-бот, другой — стремительный, как “kill -9”

Когда Лена пришла в оговоренное время, Макс загадочно улыбнулся:

— Сегодня я покажу тебе, как JOINы превращают 10к строк в 150 миллионов, чтобы потом снова выдать 10к строк… и как это можно обойти. Это реальный случай, но мы разберем его на простом синтетическом примере.

— Ой, Макс, только не начинай снова про эти твои “простые примеры”! — Лена закатила глаза, но тут же заинтересованно придвинула стул. — В прошлый раз твой “синтетический пример” заставил мой комп 10 минут гудеть вентиляторами…

Макс улыбнулся и показал ей оригинальный запрос и план выполнения:

EXPLAIN ANALYZE
SELECT count(*), avg(t1.value), max(t2.value), avg(t3.value), u.id
FROM users u
  JOIN table1 t1 ON t1.user_id = u.id
  JOIN table2 t2 ON t2.user_id = u.id
  JOIN table3 t3 ON t3.user_id = u.id
GROUP BY u.id;

Лена внимательно посмотрела на экран, и её брови поползли вверх:

— Погоди-ка… Это что, серьёзно?! — Лена ткнула пальцем в строку с actual rows=15000000. — Ты хочешь сказать, что из-за одного кривого JOIN’а мы таскаем в памяти пятнадцать миллионов строк, чтобы потом найти какой-то дурацкий максимум и потом вернуть 1000 строк?!

Макс одобрительно кивнул, скрывая улыбку — Лена начинала мыслить как настоящий performance-инженер.

— Ладно, гений, — Лена скрестила руки на груди, — и какое же твоё волшебное решение?

В её глазах читался вызов: “Ну-ка, удиви меня!”

Макс переключился на следующую вкладку. В новой версии каждая дочерняя таблица сначала сводилась до одной строки на пользователя, и только затем результаты соединялись:

EXPLAIN ANALYZE
WITH t1_agg AS (
  SELECT user_id, count(*) AS row_count, avg(value) AS avg_value
  FROM table1
  GROUP BY user_id
),
t2_agg AS (
  SELECT user_id, max(value) AS max_value
  FROM table2
  GROUP BY user_id
),
t3_agg AS (
  SELECT user_id, avg(value) AS avg_value
  FROM table3
  GROUP BY user_id
)
SELECT
  u.id,
  t1_agg.row_count,
  t1_agg.avg_value,
  t2_agg.max_value,
  t3_agg.avg_value
FROM users u
  JOIN t1_agg ON t1_agg.user_id = u.id
  JOIN t2_agg ON t2_agg.user_id = u.id
  JOIN t3_agg ON t3_agg.user_id = u.id;

— Видишь разницу? — его глаза загорелись азартом:

• Время выполнения сократилось в 73 раза (с 48 сек до 0.65 сек)

• Потребление памяти уменьшилось в 6 раз (с 5.7 Мб до 1 Мб)

В исходном эксперименте запрос строил огромный промежуточный набор, а оптимизированный вариант:

• Не перемножает строки трёх дочерних таблиц

• Агрегирует каждую таблицу только один раз

• Даёт агрегатам однозначный смысл: count относится к table1, max — к table2

Лена нахмурилась:

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

— Именно! — Макс щелкнул пальцами. — Первый подход — это как взять чеки у всех клиентов и потом раскладывать 15 млн чеков. Второй — заглянуть в картотеку каждого клиента, где уже есть нужная информация.

— Поняла, — Лена кивнула, — но почему оптимизатор сам не догадается до такого простого решения?

Макс усмехнулся:

— Потому что такое переписывание может менять смысл агрегатов. Планировщик не имеет права сам решить, что именно ты хотела посчитать. Наша задача — явно выразить нужную семантику и затем проверить план.

Лена задумалась на секунду:

— Значит, если связи «один ко многим» размножают строки, стоит проверить агрегацию до JOIN. А не слепо заменять любой GROUP BY подзапросом.

— Все именно так! — Макс удовлетворенно кивнул.

— Кстати, а если нужны будут несколько агрегатов из table2? Неужели придется делать кучу подзапросов?

Макс хитро улыбнулся:

— Об этом — поговорим в другой раз. А пока предлагаю поэкспериментировать с демо-стендом, так как практика в этом деле бесценна.

Практика

Воспроизведите ситуацию на тестовых таблицах и сравните actual rows после каждого JOIN. Если число строк резко растёт, отдельно проверьте кардинальность связей и смысл каждого агрегата.