Что такое CTE (WITH ...)? Чем WITH RECURSIVE отличается от обычного? Когда он нужен?

Question

Accepted Answer

Что такое CTE (WITH ...)

CTE (Common Table Expression) — именованный подзапрос внутри одного SQL-statement, объявляемый через WITH name AS (...). Используется для структурирования сложных запросов, повторного использования промежуточного результата и рекурсивного обхода иерархий.

Обычный WITH

-- Найти топ-10 клиентов по сумме заказов за последние 30 дней
WITH recent_orders AS (
    SELECT user_id, SUM(total) AS revenue
    FROM orders
    WHERE created_at > now() - interval '30 days'
      AND status = 'paid'
    GROUP BY user_id
)
SELECT u.email, ro.revenue
FROM recent_orders AS ro
JOIN users AS u ON u.user_id = ro.user_id
ORDER BY ro.revenue DESC
LIMIT 10;

Важно: начиная с PostgreSQL 12, нерекурсивный CTE по умолчанию не является optimization fence — планировщик может его inline-ить, если это безопасно. Для принудительного материализации используется WITH name AS MATERIALIZED (...). До PG 12 каждый CTE всегда материализовался как временный результат.

WITH RECURSIVE

Рекурсивный CTE состоит из двух частей, объединённых через UNION ALL:

Начальная (anchor) — выбирает корневые строки.
Рекурсивная — присоединяет следующий уровень, ссылаясь на само CTE.

PostgreSQL повторяет рекурсивную часть, пока она не вернёт пустой результат.

-- Обход дерева категорий (все потомки начиная от корня)
WITH RECURSIVE category_tree AS (
    -- Anchor: корневые категории
    SELECT category_id, parent_id, name, 1 AS depth,
           ARRAY[category_id] AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Рекурсивная часть: потомки
    SELECT c.category_id, c.parent_id, c.name,
           ct.depth + 1,
           ct.path || c.category_id
    FROM categories AS c
    JOIN category_tree AS ct
        ON c.parent_id = ct.category_id
    WHERE NOT c.category_id = ANY(ct.path)  -- защита от циклов
)
SELECT category_id, name, depth, path
FROM category_tree
ORDER BY depth, name;

-- Найти цепочку менеджеров сотрудника (path to root)
WITH RECURSIVE manager_chain AS (
    SELECT employee_id, manager_id, name, 0 AS level
    FROM employees
    WHERE employee_id = 42

    UNION ALL

    SELECT e.employee_id, e.manager_id, e.name, mc.level + 1
    FROM employees AS e
    JOIN manager_chain AS mc ON e.employee_id = mc.manager_id
)
SELECT employee_id, name, level
FROM manager_chain
ORDER BY level;

Когда нужен WITH RECURSIVE

Иерархии с неизвестной глубиной: категории, оргструктура, файловая система.
Обход графов: зависимости пакетов, workflow state machine.
Построение пути: shortest path, ancestry chain.

Подводные камни

Рекурсия без защиты от циклов (граф с обратными рёбрами) порождает бесконечный loop и OOM — всегда добавлять WHERE NOT id = ANY(path) или CYCLE clause (PG 14+).
Рекурсивный CTE всегда материализуется — планировщик не может его оптимизировать совместно с внешним запросом; при большом working set появляются временные файлы.
CTE с MATERIALIZED (явно или до PG12) создаёт snapshot данных: изменения, сделанные после начала statement, не видны в последующих обращениях к CTE.
Злоупотребление CTE как «заменителем» подзапроса ради читаемости без MATERIALIZED-принуждения может дать неожиданный план после PG12, если планировщик инлайнит и меняет порядок операций.
Очень глубокая рекурсия (тысячи уровней) исчерпывает work_mem и уходит в tmpfile — нужен LIMIT или breadth-first подход с явной очередью.
UNION ALL в рекурсивной части не проверяет дубли; для дедупликации нужен UNION (но он медленнее из-за сортировки) или явная проверка через path-массив.

Что такое CTE (WITH ...)? Чем WITH RECURSIVE отличается от обычного? Когда он нужен?