CTE против подзапросов

CTE (Common Table Expression, обобщённое табличное выражение) — именованный подзапрос, объявленный через WITH перед основным запросом. Живёт только во время выполнения этого запроса: в базе ничего не создаётся и после ничего не остаётся.

WITH имя_блока AS (
    SELECT ...
)
SELECT * FROM имя_блока;

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

WITH paid AS (
    SELECT * FROM orders WHERE status = 'paid'
),
revenue AS (
    SELECT user_id, SUM(sum) AS revenue
    FROM paid                          -- видит предыдущий блок
    GROUP BY user_id
)
SELECT * FROM revenue ORDER BY revenue DESC;

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

Переключай форму записи и подсвечивай шаги. Обрати внимание, что происходит с первым шагом в версии на подзапросах.

Последний пункт недооценивают. Через месяц собственный запрос читаешь как чужой, и осмысленные имена блоков экономят больше времени, чем любые комментарии.

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

-- Избыточно: один шаг, обёрнутый в WITH
WITH avg_sum AS (SELECT AVG(sum) AS a FROM orders)
SELECT * FROM orders, avg_sum WHERE sum > avg_sum.a;

-- Достаточно скалярного подзапроса
SELECT * FROM orders
WHERE sum > (SELECT AVG(sum) FROM orders);

Утверждение «CTE тормозит» до сих пор кочует по статьям и собеседованиям. Оно было верным — и перестало.

В PostgreSQL до версии 12 CTE был оптимизационным барьером: он всегда материализовался, то есть вычислялся целиком в отдельный промежуточный набор. Планировщик не мог протолкнуть внутрь условие из внешнего запроса, и CTE читал больше данных, чем нужно. Отсюда и миф.

В PostgreSQL 12 и новее поведение другое: CTE подставляется в запрос — инлайнится, — если выполняются три условия сразу.

Поведение можно задать и явно:

WITH x AS MATERIALIZED (...)      -- посчитать один раз и переиспользовать
WITH y AS NOT MATERIALIZED (...)  -- подставить в запрос как подзапрос

Единственная возможность, которой у подзапросов нет вовсе. WITH RECURSIVE состоит из двух частей, соединённых через UNION ALL: стартовой строки и правила, как получить следующую из предыдущей.

WITH RECURSIVE dates AS (
    SELECT DATE '2026-01-01' AS d          -- старт
    UNION ALL
    SELECT d + 1 FROM dates
    WHERE d < DATE '2026-01-07'          -- условие остановки
)
SELECT d FROM dates;
-- 7 строк: 01.01 … 07.01

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

Второе типовое применение — пройти по иерархии: дерево категорий, цепочка руководителей, связанные комментарии.

WITH RECURSIVE chain AS (
    SELECT id, name, 1 AS lvl
    FROM emp WHERE boss IS NULL            -- корень
    UNION ALL
    SELECT e.id, e.name, c.lvl + 1
    FROM emp e JOIN chain c ON e.boss = c.id   -- шаг вглубь
)
SELECT lvl, name FROM chain ORDER BY lvl;