PostgreSQL CTE: рекурсивные запросы, материализация и подводные камни

Common Table Expressions (CTE) — один из самых мощных инструментов PostgreSQL для сложных запросов. Но за удобством скрываются нюансы: материализация может убить производительность, рекурсия — уйти в бесконечность. Разбираем CTE от основ до подводных камней.

CTE basics — WITH для декомпозиции

CTE разбивает сложный запрос на именованные блоки:

WITH active_users AS (
    SELECT id, name FROM users
    WHERE last_login > now() - interval '30 days'
),
user_orders AS (
    SELECT u.id, u.name, count(o.id) AS order_count,
           sum(o.total) AS total_spent
    FROM active_users u
    JOIN orders o ON o.user_id = u.id
    GROUP BY u.id, u.name
)
SELECT name, order_count, total_spent
FROM user_orders
WHERE total_spent > 10000
ORDER BY total_spent DESC;

Преимущества: читаемость, переиспользование (один CTE можно использовать несколько раз), логическая декомпозиция.

Рекурсивные CTE — деревья, графы, ряды

Рекурсивный CTE: anchor (начальное условие) + recursive term (шаг), объединённые через UNION ALL:

-- Обход дерева категорий
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 AS depth,
           name::text AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id, ct.depth + 1,
           ct.path || ' > ' || c.name
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
    WHERE ct.depth < 10
)
SELECT * FROM category_tree ORDER BY path;

Поиск кратчайшего пути в графе:

WITH RECURSIVE path AS (
    SELECT target_id, ARRAY[source_id, target_id] AS route, 1 AS hops
    FROM edges WHERE source_id = 1

    UNION ALL

    SELECT e.target_id, p.route || e.target_id, p.hops + 1
    FROM path p
    JOIN edges e ON e.source_id = p.target_id
    WHERE e.target_id != ALL(p.route)
      AND p.hops < 6
)
SELECT route, hops FROM path
WHERE target_id = 42 ORDER BY hops LIMIT 1;

MATERIALIZED vs NOT MATERIALIZED

До PostgreSQL 12 CTE всегда материализовались. С версии 12 оптимизатор может инлайнить CTE в основной запрос.

-- Материализация: результат вычисляется один раз
WITH active AS MATERIALIZED (
    SELECT id FROM users WHERE status = 'active'
)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM active);

-- Инлайн: CTE подставляется как подзапрос
WITH active AS NOT MATERIALIZED (
    SELECT id FROM users WHERE status = 'active'
)
SELECT * FROM orders WHERE user_id IN (SELECT id FROM active);

MATERIALIZED полезен когда CTE используется несколько раз или нужна optimization fence. NOT MATERIALIZED — когда внешний фильтр должен протолкнуться к индексу.

Разница на EXPLAIN:

-- MATERIALIZED: CTE Scan (rows=500000), Filter: user_id=123
-- NOT MATERIALIZED: Index Scan idx_events_user_type
--   Index Cond: (user_id=123 AND type='click')

CTE vs subquery vs temp table

Бенчмарк на таблице events (10M строк, PostgreSQL 16):

Подход                    | Время  | Buffers
────────────────────────────────────────────────
CTE MATERIALIZED          | 820 ms | 45000
CTE NOT MATERIALIZED      | 2 ms   | 12
Subquery                  | 2 ms   | 12
Temp table + index        | 350 ms | 22000

Правило: CTE используется один раз + внешний фильтр → NOT MATERIALIZED или подзапрос. CTE многократно или нужна изоляция → MATERIALIZED.

Типичные ошибки

Бесконечная рекурсия — если в данных цикл, запрос не завершится:

-- Защита: ограничение глубины
WHERE depth < 100
-- Или проверка циклов
WHERE id != ALL(visited_ids)

Неожиданная материализация — CTE материализуется если: ссылается больше одного раза, содержит DML (INSERT/UPDATE/DELETE), или оптимизатор решил что так дешевле.

CTE в UPDATE/DELETE — мощный паттерн:

-- Удаление дубликатов
WITH duplicates AS (
    SELECT id, row_number() OVER (
        PARTITION BY email ORDER BY created_at DESC
    ) AS rn
    FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);

Такой CTE всегда материализуется — гарантирует консистентный снимок для DELETE.

CTE — незаменимый инструмент для сложных запросов. Главное — понимать когда материализация помогает, а когда мешает, и проверять план через EXPLAIN ANALYZE.