Материализация CTE: сдвинувшийся барьер

Барьер и день, когда он сдвинулся

Когда ты пишешь CTE, Postgres реально отделяет его от внешнего запроса или сворачивает всё вместе, давая планировщику оптимизировать свободно? Ответ изменился на одной версионной границе — и ошибиться здесь стоит секунд на каждом запросе. До Postgres 12 каждый CTE был безусловным оптимизационным барьером. Планировщик вычислял каждый CTE ровно один раз, сохранял его полный результат в памяти (материализовал) и затем выполнял внешний запрос против этого сохранённого результата. Он никогда не проталкивал фильтры внешнего запроса вниз в CTE. Это делало CTE предсказуемой, но часто дорогой границей: удобной, когда хочешь вычислить что-то один раз и переиспользовать, губительной, когда внешнему запросу нужен лишь кусочек огромного CTE.

Postgres 12 сменил дефолт на встраивание (inlining): CTE, на который ссылаются один раз, который без побочных эффектов и не рекурсивен, сворачивается во внешний запрос ровно так, будто ты написал его подзапросом — так что предикаты проталкиваются вниз, а индексы задействуются. Если хоть одно условие нарушено — на CTE ссылаются больше одного раза, или он содержит модифицирующий данные оператор (INSERT/UPDATE/DELETE ... RETURNING), или он рекурсивен — Postgres всё ещё материализует его.

-- PG12+: ссылка один раз, без побочных эффектов → встроен.
-- tenant_id = 7 проталкивается в CTE и попадает в индекс.
WITH e AS (SELECT * FROM events)
SELECT * FROM e WHERE tenant_id = 7;

-- Ссылка дважды → материализован (вычислен один раз, переиспользован).
WITH e AS (SELECT * FROM events WHERE created_at > now() - interval '1 day')
SELECT (SELECT count(*) FROM e) AS total,
       (SELECT count(*) FROM e WHERE tenant_id = 7) AS for_seven;

Два ключевых слова, переопределяющие планировщик

Ты не во власти планировщика. Два явных ключевых слова форсируют поведение:

-- Форсировать материализацию: вычислить раз, хотя ссылка один раз.
WITH e AS MATERIALIZED (SELECT * FROM big_expensive_function_call())
SELECT * FROM e WHERE id = 42;

-- Форсировать встраивание: свернуть внутрь, хотя ссылка дважды (соглашаешься на пересчёт).
WITH e AS NOT MATERIALIZED (SELECT * FROM events WHERE tenant_id = 7)
SELECT ... ;

Когда форсировать MATERIALIZED: CTE действительно дорог в вычислении (дорогая функция, тяжёлый агрегат) и ссылается один раз, но встраивание заставит эту дорогую работу выполняться на каждую внешнюю строку или повторяться; или у CTE есть побочные эффекты, которые нужно выполнить ровно один раз; или — реальное спасение — встраивание дало худший план, и ты хочешь вернуть старое поведение барьера, чтобы сломать плохой порядок join. Когда форсировать NOT MATERIALIZED: на CTE ссылаются несколько раз, поэтому Postgres его материализовал, но каждой ссылке нужен лишь отфильтрованный срез, и ты предпочёл бы проталкивание предиката материализации всего.

Ловушка режет в обе стороны. MATERIALIZED возвращает барьер, так что селективный WHERE во внешнем запросе не может протолкнуться в CTE — ты можешь просканировать всю базовую таблицу для построения CTE, а потом отфильтровать. Это ровно тот до-12 «выстрел в ногу». Используй MATERIALIZED осознанно, а не рефлекторно.

Как разглядеть это в EXPLAIN

EXPLAIN говорит, какой путь выбрал планировщик. Материализованный CTE показывает узел CTE Scan, читающий из отдельно вычисленного подплана CTE <name>; встроенный CTE не показывает CTE Scan вовсе — таблицы CTE появляются прямо в основном дереве плана, и ты увидишь внешний предикат, применённый как Index Cond глубоко внутри. Так что диагностика проста: ищи в плане CTE Scan. Есть — материализован; нет — встроен. Глубокий навык чтения этих планов — числа стоимости, оценка-против-факта по строкам, узлы join — относится к разделу 08 и к главе execution-plans трека databases; здесь нужно лишь распознать развилку.