Подзапросы и CTE (WITH), рекурсия

[Egor_DBA]

Урок 10. Подзапросы и CTE (WITH), рекурсия

В этом уроке мы учимся складывать запросы из запросов. Когда одной таблицы и одного SELECT не хватает, на помощь приходят подзапросы и общие табличные выражения (CTE, оператор WITH). Разберём, чем скалярный подзапрос отличается от табличного, как работают IN, EXISTS, ANY и ALL, что такое коррелированный подзапрос и почему он иногда бьёт по производительности. Дальше перейдём к WITH: как он делает многоэтажный запрос читаемым, что изменилось с материализацией в postgresql начиная с PG12, и как рекурсивный CTE разворачивает иерархии и графы. Демобаза - "Авиаперевозки" (схема bookings) от Postgres Professional.



Как это работает

Подзапрос - это обычный SELECT, завёрнутый в скобки и вставленный внутрь другого запроса. Планировщик postgresql выполняет его и подставляет результат туда, где он стоит. Всё дело в форме результата.

Скалярный подзапрос возвращает ровно одну строку и один столбец, то есть одно значение. Его можно ставить в SELECT, в WHERE рядом с оператором сравнения, даже в выражение. Если он вдруг вернёт две строки - запрос упадёт с ошибкой, это важная ловушка.

Табличный подзапрос возвращает набор строк. Его место - в FROM (тогда он обязан иметь псевдоним) либо справа от IN, EXISTS, ANY, ALL. Здесь логика другая: мы проверяем не равенство значению, а вхождение в множество.

IN проверяет, есть ли значение в наборе. EXISTS проверяет сам факт, что подзапрос вернул хотя бы одну строку, и ему всё равно, что именно там лежит. ANY и ALL сравнивают значение с каждым элементом набора: ANY - истина, если хоть для одного сравнение верно (по сути это обобщённый IN), ALL - если для всех. Запомните разницу с NULL: NOT IN с набором, где есть NULL, почти всегда даёт пустоту, и это источник тихих багов. EXISTS этой болезнью не страдает.

Коррелированный подзапрос ссылается на столбцы внешнего запроса. Грубо говоря, он выполняется для каждой строки внешней выборки заново. Это мощно по смыслу, но дорого: на большой таблице это легко превращается во вложенный цикл на миллионы итераций.

CTE (WITH) - это именованный временный результат, живущий в пределах одного запроса. Он не создаёт объект в базе, не пишется на диск как таблица. Главная польза - читаемость: вы раскладываете сложную логику на этажи, каждый со своим именем. До PG12 любой CTE был барьером оптимизации (всегда материализовался). Начиная с PG12 planner научился встраивать (inline) CTE, если он используется один раз и не помечен иначе. Управлять можно явно: WITH ... AS MATERIALIZED заставляет посчитать один раз и переиспользовать, AS NOT MATERIALIZED просит встроить. В PG16 и PG17 эвристики встраивания стали аккуратнее, но явные подсказки по-прежнему работают.

Рекурсивный CTE (WITH RECURSIVE) - отдельный зверь. Он состоит из якоря (стартовый набор) и рекурсивной части, которая ссылается на сам CTE. Postgres выполняет якорь, потом многократно прогоняет рекурсивную часть, пока та не перестанет давать новые строки. Так разворачиваются деревья, иерархии и графы.

SQL и примеры

Скалярный подзапрос в WHERE - рейсы, вылетающие позже среднего по всей таблице:

Код: Выделить всё

SELECT flight_no, scheduled_departure
FROM flights
WHERE scheduled_departure > (
  SELECT avg(scheduled_departure) FROM flights
);

Подзапрос в FROM (табличный) - средняя сумма брони по месяцам. Внутренний запрос отдаёт набор строк, внешний агрегирует:

Код: Выделить всё

SELECT date_trunc('month', book_date) AS mon,
       round(avg(total_amount)) AS avg_amount
FROM (
  SELECT book_date, total_amount FROM bookings
) AS b
GROUP BY 1
ORDER BY 1;

EXISTS против IN. Найдём аэропорты, из которых реально есть вылеты. EXISTS останавливается на первой найденной строке:

Код: Выделить всё

SELECT a.airport_code, a.airport_name
FROM airports a
WHERE EXISTS (
  SELECT 1 FROM flights f
  WHERE f.departure_airport = a.airport_code
);

ANY как обобщённый IN - билеты на рейсы из конкретного спис, через подзапрос:

Код: Выделить всё

SELECT ticket_no
FROM ticket_flights
WHERE flight_id = ANY (
  SELECT flight_id FROM flights WHERE status = 'Cancelled'
);

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

Код: Выделить всё

SELECT b.book_ref,
       (SELECT count(*) FROM tickets t
        WHERE t.book_ref = b.book_ref) AS tickets_cnt
FROM bookings b
ORDER BY tickets_cnt DESC
LIMIT 5;

CTE для читаемости - сначала считаем выручку по рейсам, потом фильтруем верх:

Код: Выделить всё

WITH revenue AS (
  SELECT flight_id, sum(amount) AS total
  FROM ticket_flights
  GROUP BY flight_id
)
SELECT flight_id, total
FROM revenue
WHERE total > 1000000
ORDER BY total DESC;

MATERIALIZED - явно просим посчитать тяжёлый CTE один раз:

Код: Выделить всё

WITH heavy AS MATERIALIZED (
  SELECT flight_id, count(*) AS pax
  FROM boarding_passes
  GROUP BY flight_id
)
SELECT * FROM heavy WHERE pax > 100;

Рекурсивный CTE - обход графа маршрутов. Покажем, куда можно долететь из DME за 2 пересадки:

Код: Выделить всё

WITH RECURSIVE routes AS (
  SELECT departure_airport, arrival_airport, 1 AS hops
  FROM flights
  WHERE departure_airport = 'DME'
  UNION
  SELECT r.departure_airport, f.arrival_airport, r.hops + 1
  FROM routes r
  JOIN flights f ON f.departure_airport = r.arrival_airport
  WHERE r.hops < 3
)
SELECT DISTINCT arrival_airport, min(hops) AS min_hops
FROM routes
GROUP BY arrival_airport
ORDER BY min_hops;

Классическая рекурсия по числам - генерация ряда без таблицы:

Код: Выделить всё

WITH RECURSIVE nums(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

Частые грабли

• Скалярный подзапрос вернул больше одной строки - ошибка "more than one row returned by a subquery". Добавляйте LIMIT 1 или агрегат, если это осознанно.
• NOT IN с подзапросом, где встречается NULL, отдаёт пусто. Меняйте на NOT EXISTS - он устойчив к NULL.
• Коррелированный подзапрос в SELECT на большой таблице - это скрытый цикл. Проверяйте через explain analyze и часто заменяйте на JOIN или оконную функцию.
• Подзапросу в FROM забыли псевдоним - синтаксическая ошибка. Псевдоним обязателен.
• Думают, что CTE всегда быстрее. До PG12 это барьер оптимизации; иногда обычный подзапрос или JOIN план лучше. Сравнивайте.
• Рекурсивный CTE без условия выхода и без UNION (вместо UNION ALL) на циклическом графе - бесконечная рекурсия. Ограничивайте глубину hops и отслеживайте посещённые узлы.
• EXISTS пишут как SELECT *, это не ошибка, но привычнее SELECT 1 - подчёркивает, что значения не важны.

Мини-лаба

• Подключитесь к демобазе: psql -d demo. Проверьте схему командой \dt bookings.*
• Скалярный: выведите все рейсы, чья дальность (по flights) дольше средней длительности рейса. Используйте подзапрос со средним.
• EXISTS: найдите всех пассажиров (tickets), у которых есть хотя бы один посадочный талон в boarding_passes.
• Перепишите тот же запрос через IN и сравните планы через EXPLAIN ANALYZE - где быстрее.
• Коррелированный: для каждого аэропорта посчитайте число вылетающих рейсов подзапросом в SELECT.
• CTE: соберите выручку по аэропортам вылета (через flights и ticket_flights) и оставьте топ-10.
• Рекурсия: постройте маршруты из своего любимого аэропорта за 1-2 пересадки рекурсивным WITH RECURSIVE.

Контрольные вопросы

• Чем скалярный подзапрос отличается от табличного и где каждый из них допустим в запросе?
• Почему NOT IN опасен при наличии NULL, и чем здесь лучше NOT EXISTS?
• Что делает коррелированный подзапрос и как понять, что он стал узким местом?
• Как менялось поведение CTE до и после PG12, зачем нужны MATERIALIZED и NOT MATERIALIZED?
• Из каких двух частей состоит рекурсивный CTE и как он понимает, что пора остановиться?
• В чём разница между UNION и UNION ALL внутри WITH RECURSIVE при обходе графа с циклами?

[bonds]

А правда что после PG12 уже не надо боятся WITH как барьера? У меня запрос с CTE на проде стал быстрее когда я просто переписал его в подзапрос в FROM, не пойму когда что лучше

[sparkguru]

поймал бесконечную рекурсию на графе рейсов, спасло добавление условия hops < 3 и UNION вместо UNION ALL, теперь дубли путей отсекаются сами