Как оптимизировать медленный агрегирующий SQL-запрос с дорогими JOIN на большой таблице?

Короткий ответ

Основные рычаги — сузить объём данных до агрегации (фильтры по партиции/дате до JOIN), агрегировать большую таблицу до соединения с другими вместо соединения «сырых» строк, использовать подходящие индексы или ключи сортировки/партиционирования, и проверять план выполнения запроса, а не угадывать узкое место.

Как это работает подробнее

если агрегация нужна только после JOIN нескольких больших таблиц, часто эффективнее сначала агрегировать каждую таблицу отдельно (уменьшив число строк) и только потом соединять результаты — это снижает объём данных, участвующих в самом дорогом этапе. План выполнения (EXPLAIN/EXPLAIN ANALYZE в PostgreSQL, аналоги в других СУБД) показывает, где реально тратится время — сканирование таблицы целиком, дорогая сортировка для JOIN, или сам JOIN.

Sanity check: сравнить EXPLAIN ANALYZE обоих вариантов — во втором варианте JOIN должен выполняться над заметно меньшим числом строк (agregated по user_id), что должно сократить общее время выполнения.

Пример кода

example.sqlКопировать

-- Медленно: JOIN "сырых" таблиц с последующей агрегацией
SELECT u.country, COUNT(*) FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.order_date >= '2024-01-01'
GROUP BY u.country;
-- Быстрее: агрегировать orders по user_id до JOIN с users
WITH order_counts AS (
  SELECT user_id, COUNT(*) AS cnt
  FROM orders
  WHERE order_date >= '2024-01-01'
  GROUP BY user_id
)
SELECT u.country, SUM(oc.cnt)
FROM order_counts oc
JOIN users u ON u.id = oc.user_id
GROUP BY u.country;

Что использовать на практике

• В PostgreSQL помогает наличие индексов на полях соединения и фильтрации, а также своевременный VACUUM/ANALYZE для актуальной статистики планировщика. В колоночных СУБД (ClickHouse, BigQuery) важнее порядок ключа партиционирования/сортировки и избегание полного скана таблицы — фильтрация должна попадать на партиционированное поле. Материализованные представления или предрасчитанные агрегаты полезны для часто повторяющихся тяжёлых запросов, если приемлема задержка актуальности данных. Также полезно проверять статистику планировщика запросов после крупных изменений объёма данных — устаревшая статистика может заставлять оптимизатор выбирать неэффективный план даже при наличии подходящих индексов.

Подводные камни

• Добавлять SELECT * и тянуть ненужные колонки, увеличивая объём данных для сортировки/передачи
• Делать JOIN до фильтрации по дате, из-за чего СУБД сканирует значительно больше партиций, чем нужно
• Оптимизировать запрос "на глаз" без просмотра плана выполнения, меняя случайные части запроса. Ещё одна ошибка — добавлять индексы бездумно на все часто используемые колонки, не учитывая, что каждый дополнительный индекс замедляет операции записи и увеличивает объём хранения.