Ускоряем медленный запрос: агрегировать до JOIN, фильтровать раньше, EXISTS вместо IN

Бран · продюсер «Хроник Пограничья»

Карточка игрока в дашборде грузится пять секунд. Аналитик говорит, что нужен сервер помощнее. Индексы разработчики уже повесили. Можно что-то сделать, не покупая железо?

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

Правило у всех переписываний одно: быстрый запрос обязан давать тот же правильный ответ. Каждый приём ниже проверяем двумя вопросами: стало ли быстрее и не изменился ли ответ.

Все числа сняты EXPLAIN ANALYZE на базе guild в PGlite. Миллисекунды зависят от машины и скачут от запуска к запуску, поэтому для каждого запроса даём разброс трёх прогонов. На твоём ноутбуке они будут другими, а соотношение останется.

Агрегировать до JOIN

Как устроена медленная карточка

Вот запрос аналитика. По каждому игроку: сколько квестов он завершил, сколько у него сессий и сколько он заплатил.

SELECT
  p.id AS player_id,
  p.nickname,
  count(DISTINCT qr.id) AS completed_runs,
  count(DISTINCT s.id) AS sessions,
  coalesce(sum(DISTINCT pu.amount_rub), 0) AS revenue
FROM players AS p
LEFT JOIN characters AS c ON c.player_id = p.id
LEFT JOIN quest_runs AS qr ON qr.character_id = c.id AND qr.status = 'completed'
LEFT JOIN sessions AS s ON s.player_id = p.id
LEFT JOIN purchases AS pu ON pu.player_id = p.id AND pu.status = 'paid'
GROUP BY p.id, p.nickname;

Представь склад, где надо посчитать коробки и мешки на каждой полке. Можно пересчитать коробки, потом мешки, и записать два числа. А можно сначала разложить на полу все пары «коробка и мешок» с одной полки, а потом считать, сколько в этих парах разных коробок. Ответ про коробки выйдет тот же, только пар будет коробки, умноженные на мешки.

Так и здесь. Все четыре таблицы соединяются по игроку, и у игрока с 20 квестами, 10 сессиями и 3 покупками получается 20 × 10 × 3 = 600 строк. Это размножение строк, знакомое с блока D. Аналитик увидел раздутые числа и поставил DISTINCT в каждый агрегат. Числа стали похожи на правду, а работа осталась:

GroupAggregate  (actual time=188.548..4903.975 rows=4092.00 loops=1)
  ->  Incremental Sort  (actual time=186.495..3521.433 rows=1343415.00 loops=1)
  ...
Execution Time: 4907.383 ms

Ради 4092 строк ответа база собрала и отсортировала 1 343 415 промежуточных строк. Три прогона дали 4,7-4,9 секунды.

Переписываем

Сворачиваем каждую таблицу до одной строки на игрока отдельно и только потом соединяем:

WITH runs AS (
  SELECT
    c.player_id,
    count(*) AS completed_runs
  FROM quest_runs AS qr
  INNER JOIN characters AS c ON c.id = qr.character_id
  WHERE qr.status = 'completed'
  GROUP BY c.player_id
),

player_sessions AS (
  SELECT player_id, count(*) AS sessions
  FROM sessions
  GROUP BY player_id
),

spend AS (
  SELECT player_id, sum(amount_rub) AS revenue
  FROM purchases
  WHERE status = 'paid'
  GROUP BY player_id
)

SELECT
  p.id AS player_id,
  p.nickname,
  coalesce(r.completed_runs, 0) AS completed_runs,
  coalesce(ps.sessions, 0) AS sessions,
  coalesce(sp.revenue, 0) AS revenue
FROM players AS p
LEFT JOIN runs AS r ON r.player_id = p.id
LEFT JOIN player_sessions AS ps ON ps.player_id = p.id
LEFT JOIN spend AS sp ON sp.player_id = p.id;

Hash Left Join  (actual time=144.255..153.458 rows=4092.00 loops=1)
  ...
Execution Time: 155.437 ms

0,16-0,20 секунды вместо 4,7-4,9, в 25-30 раз быстрее. Каждая CTE читает свою таблицу один раз, и каждая строка игрока соединяется ровно с одной строкой каждой CTE. Размножаться нечему, поэтому и DISTINCT больше не нужен.

А ответ?

Сравниваем со старой версией. Счётчики квестов и сессий совпали у всех игроков: count(DISTINCT qr.id) спасал их, потому что считал по ключу. А выручка разошлась у 87 игроков, и по всей базе старая версия насчитала 489 717 ₽ вместо 705 070 ₽.

Виноват sum(DISTINCT pu.amount_rub). Он складывает разные суммы, а не разные покупки: две покупки «Мешка гемов» по 499 ₽ превращаются в одну. Медленный запрос был ещё и неправильным. Это частая история: DISTINCT, поставленный против размножения, лечит одно число и тихо ломает соседнее. Правильное лечение убирает причину, то есть соединение до агрегации.

Фильтровать раньше

Второй совет из каждой статьи про оптимизацию: отбрасывай лишние строки как можно раньше. Проверим, что он даёт в PostgreSQL.

Условие в WHERE на столбец таблицы планировщик и так опускает к чтению этой таблицы, как бы ты ни расставил JOIN. Переставлять условия внутри одного WHERE ради скорости бессмысленно. Работает другое: не считать то, что потом выбросишь.

Отчёт по новичкам апреля: сколько квестов завершил каждый игрок, зарегистрированный в апреле. Первая версия сворачивает квесты всех 3362 игроков, у которых есть завершённые квесты, и потом соединяет с апрельскими. Вторая сначала выбирает апрельских игроков в CTE и сворачивает квесты только их персонажей. На нашей базе разница почти в шуме: 76-127 мс против 67-79. Таблицу quest_runs обе версии всё равно читают целиком, индекса для поиска по игроку нет, а сама группировка дешёвая. Ранний фильтр окупается, когда он уменьшает то, что идёт в тяжёлый JOIN или в сортировку, или когда по нему может сработать индекс.

Отсюда второе правило: фильтр по дате пиши диапазоном по голой колонке. Даже без индекса date_trunc('month', started_at) = '2026-04-01' вычисляет функцию для каждой из 36 348 строк и в наших прогонах занял 43-95 мс, диапазон started_at >= '2026-04-01' AND started_at < '2026-05-01' 29-41 мс. А с индексом по started_at разница станет такой же, как в прошлом уроке.