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

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

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

Именно эта ссылка и создаёт стоимость. Логически база должна для каждой внешней строки заново вычислить внутренний запрос со своим значением параметра. Планировщик умеет разворачивать часть таких конструкций в соединения, но далеко не все: скалярный подзапрос в 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 (...). Попытка засунуть арифметику внутрь коррелированного подзапроса обычно и делает его нечитаемым.

В учебной базе заказов нет, их роль играют оплаты: в payments 1 251 строка, и у 339 из них есть предыдущая оплата того же пользователя. Оба запроса ниже возвращают одни и те же десять строк — потому что двух оплат в один день у пользователя нет: иначе lag вернул бы ту же дату, а строгое < — более раннюю.

SELECT p.payment_id, p.user_id, p.paid_at,
  (SELECT max(prev.paid_at)
   FROM payments prev
   WHERE prev.user_id = p.user_id
     AND prev.paid_at < p.paid_at) AS previous_paid_at
FROM payments p
ORDER BY p.user_id, p.paid_at, p.payment_id
LIMIT 10;

SELECT payment_id, user_id, paid_at,
       lag(paid_at) OVER (
         PARTITION BY user_id
         ORDER BY paid_at, payment_id
       ) AS previous_paid_at
FROM payments
ORDER BY user_id, paid_at, payment_id
LIMIT 10;

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

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

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

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

WITH correlated AS (            -- подзапрос видит всю таблицу
  SELECT p.payment_id,
    (SELECT max(prev.paid_at) FROM payments prev
     WHERE prev.user_id = p.user_id AND prev.paid_at < p.paid_at) AS prev_at
  FROM payments p
  WHERE p.paid_at >= DATE '2026-08-01'
),
window_after_where AS (         -- окно видит только август
  SELECT payment_id,
         lag(paid_at) OVER (PARTITION BY user_id ORDER BY paid_at, payment_id) AS prev_at
  FROM payments
  WHERE paid_at >= DATE '2026-08-01'
),
window_then_filter AS (         -- окно по всей истории, фильтр во внешнем слое
  SELECT payment_id, prev_at
  FROM (
    SELECT payment_id, paid_at,
           lag(paid_at) OVER (PARTITION BY user_id ORDER BY paid_at, payment_id) AS prev_at
    FROM payments
  ) AS history
  WHERE paid_at >= DATE '2026-08-01'
)
SELECT count(*)         AS august_payments,
       count(c.prev_at) AS correlated_found,
       count(a.prev_at) AS window_after_where_found,
       count(t.prev_at) AS window_then_filter_found
FROM correlated c
JOIN window_after_where a ON a.payment_id = c.payment_id
JOIN window_then_filter t ON t.payment_id = c.payment_id;

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

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

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

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

WITH user_totals AS (           -- считаем один раз
  SELECT user_id,
         sum(amount)  AS lifetime_amount,
         count(*)     AS payments_count
  FROM payments
  GROUP BY user_id
)
SELECT u.user_id,
       coalesce(t.lifetime_amount, 0) AS lifetime_amount,
       coalesce(t.payments_count, 0)  AS payments_count
FROM users u
LEFT JOIN user_totals t ON t.user_id = u.user_id
ORDER BY u.user_id
LIMIT 10;

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

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

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

WITH correlated AS (
  SELECT p.payment_id,
    (SELECT max(prev.paid_at) FROM payments prev
     WHERE prev.user_id = p.user_id AND prev.paid_at < p.paid_at) AS prev_at
  FROM payments p
),
windowed AS (
  SELECT payment_id,
         lag(paid_at) OVER (PARTITION BY user_id ORDER BY paid_at, payment_id) AS prev_at
  FROM payments
)
SELECT count(*)         AS payments,
       count(c.prev_at) AS with_previous,
       count(*) FILTER (WHERE c.prev_at IS DISTINCT FROM w.prev_at) AS mismatches
FROM correlated c
JOIN windowed w ON w.payment_id = c.payment_id;

Как читать EXPLAIN ANALYZE: время у узла указано на один повтор

Снимем план с коррелированной версии из сверки — по всей таблице оплат, в PostgreSQL 14: в песочнице симулятора EXPLAIN закрыт. В выводе важны две строки. Внешняя: Seq Scan on payments p ... rows=1251 loops=1. Внутренняя, под заголовком SubPlan 1: Seq Scan on payments prev ... actual time=0.033..0.043 rows=0 loops=1251.

Здесь и спрятана главная сложность чтения плана. Сотые доли миллисекунды выглядят безобидно, но это время одного повтора, а не всего узла. Полная стоимость — произведение: в этом запуске 0,043 мс × 1 251 ≈ 54 мс, почти всё время запроса. В другом запуске миллисекунды будут другими, арифметика — той же. В условном плане с продакшена из вступления те же строки выглядели бы как rows=14812 loops=1 и actual time=0.9..31.1 rows=8 loops=14812: 31,1 мс × 14 812 ≈ 461 000 мс, те самые без малого восемь минут. Про loops как число повторов сказано выше; практический вывод в другом — столбец времени нельзя читать как итог, его нужно умножать на loops, и только строка Execution Time в конце вывода показывает факт.

Индекс меняет множитель, а не структуру. С индексом по (user_id, paid_at) внутренний узел становится Index Only Scan, повторов остаётся 1 251, а запрос на учебной базе ускоряется в десятки раз: порядка миллисекунды вместо десятков. Форма при этом осталась прежней: вырастет таблица — вырастет и число повторов. В плане оконной версии SubPlan исчезает вовсе, а вместо него появляются WindowAgg над Sort с loops=1 — это и есть «один проход», про который шла речь в разделе про замену окном.

Второе, что стоит найти в плане, — расхождение оценки и факта. Запись вида rows=120 в оценке против rows=840000 в факте означает, что планировщик выбирал стратегию под сто двадцать строк, а получил почти миллион: отсюда и вложенный цикл там, где нужен был hash join. Причины обычно бытовые. Статистика устарела — помогает ANALYZE по таблице. Фильтр написан через выражение вроде date_trunc('month', paid_at) — для оценки он непрозрачен, и лучше сравнивать саму колонку с границами периода. Два условия по скоррелированным колонкам (город и страна, тариф и валюта) оцениваются как независимые, и селективность перемножается в ничто.

Третье — как прошла сортировка. Sort Method: quicksort Memory: 107kB — так в плане оконной версии на учебной базе — значит, что она уложилась в память. external merge с пометкой Disk — что она уехала в временные файлы, и дальше разговор уже не про форму запроса, а про work_mem и объём строк, которые до сортировки стоило бы сократить. Рядом в выводе с BUFFERS стоят два счётчика: shared hit — страницы, прочитанные из кэша, shared read — с диска. В нашем плане у внутреннего узла shared hit=12510: десять страниц таблицы, прочитанные 1 251 раз. Именно эти счётчики объясняют, почему один и тот же запрос два раза подряд показывает разное время.

У самой диагностики есть цена. EXPLAIN ANALYZE действительно выполняет запрос: на восьмиминутном отчёте вы заплатите восемь минут, а на UPDATE или DELETE — изменением данных, поэтому такие команды оборачивают в BEGIN и закрывают ROLLBACK. Чистый EXPLAIN бесплатен, но показывает только оценки — ни actual rows, ни loops в нём нет, то есть ровно того, что мы ищем. Разумный компромисс — снять полный план на выборке за один день: число повторов и форма цикла видны, а ждать восемь минут не нужно.

И одна ловушка, на которую легко потратить вечер: cost измеряется в условных единицах планировщика, а не в миллисекундах. Сравнить cost двух вариантов одного запроса между собой можно, перевести его в секунды — нет. Вывод «стало в три раза дешевле по cost» ничего не обещает пользователю дашборда, пока рядом не стоят два замера Execution Time.

explain (analyze, buffers)
select p.payment_id,
  (select max(prev.paid_at)
   from payments prev
   where prev.user_id = p.user_id
     and prev.paid_at < p.paid_at) as previous_paid_at
from payments p;

-- PostgreSQL 14, учебная база. Время — один из запусков, у вас оно будет другим;
-- rows, loops и число страниц совпадут.
-- Seq Scan on payments p  (actual time=0.062..54.340 rows=1251 loops=1)
--   SubPlan 1
--     ->  Aggregate  (actual time=0.043..0.043 rows=1 loops=1251)
--           ->  Seq Scan on payments prev
--                        (actual time=0.033..0.043 rows=0 loops=1251)
--                 Rows Removed by Filter: 1251
--                 Buffers: shared hit=12510
-- Execution Time: 54.452 ms
-- 0,043 мс на один повтор × 1 251 повтор ≈ 54 мс

Чеклист

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

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