Коррелированный подзапрос: как он выполняется и чем его заменить

Что делает подзапрос коррелированным

Обычный подзапрос самодостаточен: его можно выполнить отдельно, и он вернёт один и тот же результат независимо от внешнего запроса. Коррелированный подзапрос так выполнить нельзя — внутри он ссылается на колонку внешней строки, и без неё выражение не имеет смысла.

Именно эта ссылка и создаёт стоимость. Логически база должна для каждой внешней строки заново вычислить внутренний запрос со своим значением параметра. Планировщик умеет разворачивать часть таких конструкций в соединения, но далеко не все: скалярный подзапрос в SELECT обычно остаётся тем, чем выглядит, — вложенным циклом.

Отличить одно от другого просто. Если из подзапроса убрать условие со ссылкой на внешнюю таблицу и он всё ещё осмыслен — корреляции нет. Если без этого условия подзапрос превращается в бессмыслицу вроде «максимум по всем заказам вообще» — корреляция есть.

Откуда берётся резкий рост времени

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

Отсюда характерный симптом — производительность падает не плавно, а обрывом. Отчёт работал полгода, потом за неделю стал открываться вдвое дольше, а ещё через месяц перестал укладываться в таймаут. Ни одного изменения в коде при этом не было.

Индекс по ключу корреляции — первое, что стоит проверить: без него каждый внутренний запуск превращается в сканирование таблицы, и множитель становится катастрофическим. Но индекс лишь смягчает проблему, а не убирает её: запусков всё равно остаётся столько, сколько внешних строк.

Где корреляция — правильный инструмент

Переписывать всё подряд не нужно. Есть два случая, где коррелированная форма и читается лучше, и работает нормально.

Первый — проверка существования через EXISTS. Планировщик обычно превращает её в semi-join: он останавливается на первом совпадении и не строит полный результат подзапроса. Это одна из самых дешёвых конструкций в SQL, и заменять её на JOIN с последующим DISTINCT почти всегда хуже.

Второй — LATERAL, когда для каждой строки нужны несколько полей «подходящей» записи: последний заказ, первое обращение в поддержку, ближайшее по времени событие. Формально это тоже вложенный цикл, но с LIMIT 1 и индексом по ключу и дате он выполняется быстро, а альтернатива через окна получается длиннее и хуже читается.

• Проверка «есть ли хотя бы одна строка» — EXISTS, менять не нужно.
• Исключение по списку — NOT EXISTS, устойчиво к NULL в отличие от NOT IN.
• Несколько полей одной подходящей записи — LATERAL с ORDER BY и LIMIT 1.
• Одно агрегированное значение на каждую строку большой таблицы — вот это стоит переписать.

Замена оконной функцией

Задача «предыдущее значение в последовательности» — самый частый кандидат на переписывание. Коррелированный max(...) с условием «меньше текущей даты» выражает ровно то же, что lag() по упорядоченному окну, но окно считается за один проход по данным.

При переходе важно не потерять детерминизм. Если у пользователя два заказа с одинаковым timestamp, порядок в окне без тай-брейкера не определён, и «предыдущий» заказ может меняться от запуска к запуску. Добавьте в ORDER BY окна стабильный ключ — обычно id.

Тем же приёмом решается целое семейство задач: lag и lead дают соседние значения, first_value и last_value — границы окна, а sum(...) OVER (ORDER BY ...) считает накопительный итог. Всё это раньше писалось коррелированными подзапросами и до сих пор встречается в старых витринах — обычно именно там, где отчёт стал медленным.

Ещё одна деталь про интервалы. Если между заказами нужно посчитать не только факт, но и разрыв в днях, вычитайте даты уже после расчёта окна: paid_at - lag(paid_at) OVER (...). Попытка засунуть арифметику внутрь коррелированного подзапроса обычно и делает его нечитаемым.

-- было: подзапрос выполняется для каждой строки
SELECT o.order_id, o.user_id, o.paid_at,
  (SELECT max(prev.paid_at)
   FROM orders prev
   WHERE prev.user_id = o.user_id
     AND prev.paid_at < o.paid_at) AS previous_paid_at
FROM orders o
WHERE o.status = 'paid';

-- стало: один проход, порядок задан явно
SELECT order_id, user_id, paid_at,
       lag(paid_at) OVER (
         PARTITION BY user_id
         ORDER BY paid_at, order_id
       ) AS previous_paid_at
FROM orders
WHERE status = 'paid';

Незаметная ловушка: окно видит только отфильтрованные строки

Два запроса выше выглядят эквивалентными, но это не всегда так. Оконная функция вычисляется после WHERE, поэтому lag() возьмёт предыдущий заказ среди тех строк, которые прошли фильтр. Коррелированный подзапрос в первом варианте смотрит в таблицу orders целиком и найдёт предыдущий заказ даже со статусом «отменён».

Пока фильтр в обеих частях одинаковый, разницы нет. Но стоит добавить во внешний запрос ещё одно условие — скажем, ограничить период последним месяцем, — и результаты разойдутся: у первого заказа месяца «предыдущего» не окажется, хотя в жизни он был.

Поэтому при переписывании полезно явно ответить на вопрос: предыдущий среди чего? Если среди всех заказов пользователя — фильтр по периоду должен применяться после расчёта окна, во внешнем слое. Если среди оплаченных — оставляем как есть и фиксируем это в комментарии.

Замена предварительной агрегацией

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

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

Здесь тоже есть нюанс с семантикой. LEFT JOIN оставит пользователей без заказов с NULL, а коррелированный подзапрос вернёт для них NULL или ноль в зависимости от агрегата: max даст NULL, а count — ноль. Если число попадает в деление, разница между NULL и нулём перестаёт быть теоретической.

WITH user_totals AS (           -- считаем один раз
  SELECT user_id,
         sum(amount)  AS lifetime_amount,
         count(*)     AS orders_count
  FROM orders
  WHERE status = 'paid'
  GROUP BY user_id
)
SELECT u.user_id,
       coalesce(t.lifetime_amount, 0) AS lifetime_amount,
       coalesce(t.orders_count, 0)    AS orders_count
FROM users u
LEFT JOIN user_totals t ON t.user_id = u.user_id;

Как убедиться, что стало лучше

Спор о производительности решается не рассуждением, а планом выполнения. EXPLAIN ANALYZE показывает не только время, но и число повторов узла — в выводе это loops. Если внутренний узел выполнился десятки тысяч раз, вы смотрите ровно на ту проблему, ради которой затевалось переписывание.

Сравнивать варианты нужно на реалистичном объёме и с прогретым кэшем: первый запуск почти всегда медленнее, и на маленькой выборке разница вообще не видна. И обязательно сверьте результаты двух версий построчно — переписывание считается удачным, только если числа совпали.

WITH correlated AS (
  SELECT o.order_id,
    (SELECT max(prev.paid_at) FROM orders prev
     WHERE prev.user_id = o.user_id AND prev.paid_at < o.paid_at) AS prev_at
  FROM orders o
  WHERE o.status = 'paid'
),
windowed AS (
  SELECT order_id,
         lag(paid_at) OVER (PARTITION BY user_id ORDER BY paid_at, order_id) AS prev_at
  FROM orders
  WHERE status = 'paid'
)
SELECT count(*) AS mismatches
FROM correlated c
JOIN windowed w USING (order_id)
WHERE c.prev_at IS DISTINCT FROM w.prev_at;

Чеклист

Коррелированный подзапрос — не антипаттерн. Это конструкция с понятной ценой, которую стоит платить осознанно.

• Подзапрос действительно коррелирован — в нём есть ссылка на внешнюю строку?
• Если это проверка наличия, используется EXISTS, а не пересчёт агрегата?
• Если нужно одно значение на сущность, оно считается один раз в отдельном слое?
• При замене на окно сохранился тот же набор строк, среди которых ищется «предыдущий»?
• В ORDER BY окна есть тай-брейкер для одинаковых меток времени?
• Результаты старой и новой версии сверены построчно, а не только по итоговой сумме?