CTE против подзапроса и представления

Одна логика, три места, три времени жизни

Зачем вообще думать о форме, если все три дают одинаковые строки? Потому что время жизни определяет переиспользуемость — и, как показывает остаток урока, производительность. Один и тот же подзапрос может выступать в трёх структурно разных формах. Возьмём «категории, чей родитель — корень» над categories(id, parent_id):

-- 1. Инлайн / производная таблица: живёт в FROM одного оператора
SELECT c.id
FROM categories c
JOIN (SELECT id FROM categories WHERE parent_id IS NULL) roots
  ON c.parent_id = roots.id;

-- 2. CTE: именованный, всё ещё ограничен одним оператором
WITH roots AS (SELECT id FROM categories WHERE parent_id IS NULL)
SELECT c.id
FROM categories c
JOIN roots ON c.parent_id = roots.id;

-- 3. Представление: именованное, хранимое в каталоге, переиспользуемое между операторами
CREATE VIEW roots AS SELECT id FROM categories WHERE parent_id IS NULL;
SELECT c.id FROM categories c JOIN roots ON c.parent_id = roots.id;  -- позже, где угодно

Ось, которая их различает, — время жизни и видимость. У инлайн-подзапроса нет имени, и он существует только в своей позиции в одном операторе. У CTE есть имя, но он всё ещё ограничен одним оператором — когда оператор завершается, имя исчезает. У представления имя хранится в каталоге: оно сохраняется, и любой будущий запрос может из него читать. Скалярный подзапрос — четвёртая форма: подзапрос в скобках, возвращающий ровно одну строку и один столбец, годный везде, где ожидается одно значение, как WHERE created_at = (SELECT max(created_at) FROM orders).

Когда обычный подзапрос — более ясный выбор

CTE не лучше автоматически. Подзапрос в FROM часто яснее, когда логика используется ровно один раз и мала — обернуть трёхстрочный фильтр в WITH добавляет церемонии без ясности. Скалярный подзапрос в SELECT или WHERE — естественная форма для «сравни каждую строку с одним агрегатным значением»; переписывание его как CTE обычно читается хуже. А коррелированный подзапрос — тот, что ссылается на внешнюю строку, как WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) — не имеет чистого эквивалента-CTE, потому что по замыслу выполняется на каждую внешнюю строку. Используй форму, которая называет то, что заслуживает имени, и инлайнит то, что нет.

Оговорка, указывающая на урок 03

Вот часть, что превратила спор на ревью из вкусовщины в суть. Бóльшую часть истории Postgres CTE был безусловным оптимизационным барьером: планировщик всегда вычислял CTE отдельно и материализовал его результат, отказываясь проталкивать предикаты из внешнего запроса внутрь него. Эквивалентный инлайн-подзапрос барьером не был — планировщик мог его расплющить, протолкнуть фильтры внутрь и выбрать индексы соответственно. Так что одна и та же логика могла выполняться очень по-разному в зависимости от выбранной формы, и форма-CTE порой была драматически медленнее.

-- До PG12: этот CTE материализовался целиком, потом фильтровался — WHERE
-- НЕ мог протолкнуться в CTE, поэтому сканировал куда больше строк, чем нужно.
WITH all_events AS (SELECT * FROM events)        -- миллионы строк материализованы
SELECT * FROM all_events WHERE tenant_id = 7;    -- фильтр применён ПОСЛЕ

-- Эквивалентный подзапрос позволил планировщику протолкнуть tenant_id = 7 в индекс.
SELECT * FROM (SELECT * FROM events) e WHERE tenant_id = 7;

Postgres 12 изменил этот дефолт, а ключевые слова MATERIALIZED / NOT MATERIALIZED позволяют управлять этим явно. Вся эта история — что изменилось, когда материализация всё ещё помогает и как прочитать это в EXPLAIN — это урок 03. Пока вывод такой: CTE против подзапроса не чисто косметика; исторически это влияло на план, а на старых серверах влияет до сих пор.