CTE и рекурсивные запросы в PostgreSQL

CTE и рекурсивные запросы в PostgreSQL

Главная идея

Как это выглядит на практике

1. Задача: построить страницу профиля клиента с суммой заказов, количеством, средним чеком и последней покупкой. Первое решение: 4 отдельных запроса в Ruby. Понимаешь что это неэффективно — переносишь в CTE.
2. Пишешь WITH order_stats AS (SELECT COUNT(*), SUM(amount), AVG(amount), MAX(created_at) FROM orders WHERE user_id = $1 GROUP BY user_id). Один запрос, один round-trip, результат совпадает.
3. Появляется требование: показать дерево категорий для сайдбара магазина. Категории вложены на 4-5 уровней. Первое решение в коде: рекурсивная функция, которая делает SELECT для каждого узла — 200+ запросов при 200 категориях.
4. Узнаёшь про рекурсивный CTE. Пишешь WITH RECURSIVE cats AS (корень UNION ALL дочерние). Один запрос возвращает всё дерево — 200 узлов за 3ms вместо 500ms и 200 round-trips.
5. Появляется задача перенести обработанные заказы из staging-таблицы в основную и одновременно залогировать операцию. Нужна атомарность. Data-modifying CTE: WITH deleted AS (DELETE FROM staging RETURNING *) INSERT INTO orders SELECT * FROM deleted, INSERT INTO log SELECT id, NOW() FROM deleted.
6. На code review замечаешь что коллега написал запрос с 5 уровнями вложенных подзапросов. Показываешь как переписать в CTE — код уменьшается вдвое и становится читаемым.
7. Нужно найти всех подчинённых сотрудника в org-структуре (произвольная глубина). Рекурсивный CTE с накоплением пути: ancestors || employee_id позволяет построить полную иерархию.
8. Обнаруживаешь медленный запрос: CTE используется дважды в запросе, но PostgreSQL вычисляет его дважды (NOT MATERIALIZED в Postgres 12+). Добавляешь MATERIALIZED — CTE вычисляется один раз, производительность растёт в 2 раза.
9. Задача: для каждого товара найти все родительские категории (breadcrumbs). Рекурсивный CTE снизу вверх: начинаешь с category_id товара, идёшь по parent_id до корня. Массив ancestors || id накапливает путь.
10. Интеграция с внешней системой: массовый upsert пользователей из CSV. Data-modifying CTE с ON CONFLICT DO UPDATE + RETURNING (xmax = 0) позволяет в одном запросе узнать сколько создано и сколько обновлено.
11. Генерация серии дат для отчёта: WITH RECURSIVE dates AS (SELECT '2024-01-01'::DATE AS d UNION ALL SELECT d + 1 FROM dates WHERE d < '2024-01-31') SELECT d FROM dates — все даты января без generate_series.
12. На собеседовании просят написать запрос для поиска всех возможных путей в графе между двумя узлами. Рекурсивный CTE с накоплением visited nodes и проверкой на циклы через CYCLE clause.

Примеры кода

CTE-цепочка: разбивка сложной логики на шаги

-- Отчёт: топ клиенты за квартал с их сегментом и регионом
WITH
-- Шаг 1: заказы за текущий квартал
quarterly_orders AS (
  SELECT
    user_id,
    COUNT(*)        AS order_count,
    SUM(amount)     AS total_spent,
    MAX(created_at) AS last_order_at
  FROM orders
  WHERE created_at >= DATE_TRUNC('quarter', NOW())
    AND status = 'completed'
  GROUP BY user_id
),
-- Шаг 2: определяем сегмент клиента
segmented AS (
  SELECT
    user_id,
    order_count,
    total_spent,
    last_order_at,
    CASE
      WHEN total_spent >= 100000 THEN 'VIP'
      WHEN total_spent >= 30000  THEN 'Premium'
      WHEN total_spent >= 10000  THEN 'Regular'
      ELSE 'New'
    END AS segment
  FROM quarterly_orders
),
-- Шаг 3: ранжируем внутри сегмента
ranked AS (
  SELECT
    s.*,
    u.email,
    u.name,
    u.country,
    ROW_NUMBER() OVER (PARTITION BY s.segment ORDER BY s.total_spent DESC) AS rank_in_segment
  FROM segmented s
  JOIN users u ON u.id = s.user_id
  WHERE u.is_active = true
)
-- Финальный результат: топ-3 в каждом сегменте
SELECT *
FROM ranked
WHERE rank_in_segment <= 3
ORDER BY segment, rank_in_segment;

Рекурсивный CTE: обход дерева категорий

-- Таблица: categories(id, name, slug, parent_id)
-- Задача: получить полное поддерево для категории 'electronics'

WITH RECURSIVE category_tree AS (
  -- Базовый случай: корневая категория
  SELECT
    id,
    name,
    slug,
    parent_id,
    0           AS depth,
    name::TEXT  AS full_path,
    ARRAY[id]   AS ancestors
  FROM categories
  WHERE slug = 'electronics'

  UNION ALL

  -- Рекурсивная часть: дочерние категории текущего уровня
  SELECT
    c.id,
    c.name,
    c.slug,
    c.parent_id,
    ct.depth + 1,
    ct.full_path || ' > ' || c.name,
    ct.ancestors || c.id
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
  WHERE ct.depth < 8  -- ограничение глубины
)
SELECT
  id,
  name,
  depth,
  full_path,
  -- Можно подсчитать товары в каждой ветке
  (SELECT COUNT(*) FROM products p WHERE p.category_id = category_tree.id AND p.is_active) AS product_count
FROM category_tree
ORDER BY full_path;

-- PostgreSQL 14+: встроенная защита от циклов (для графов с циклами)
WITH RECURSIVE graph_traversal AS (
  SELECT id, parent_id, name, 0 AS depth
  FROM nodes WHERE id = 1

  UNION ALL

  SELECT n.id, n.parent_id, n.name, gt.depth + 1
  FROM nodes n
  JOIN graph_traversal gt ON n.parent_id = gt.id
  WHERE gt.depth < 20
)
CYCLE id SET is_cycle TO true DEFAULT false USING path
SELECT id, name, depth, is_cycle, path
FROM graph_traversal
WHERE NOT is_cycle;

Data-modifying CTE и materialization

-- Атомарный перенос данных: из staging в production + логирование
WITH
moved_orders AS (
  DELETE FROM orders_staging
  WHERE processed_at IS NULL
    AND created_at < NOW() - INTERVAL '1 hour'
  RETURNING *
),
inserted AS (
  INSERT INTO orders (user_id, amount, status, created_at)
  SELECT user_id, amount, 'pending', created_at
  FROM moved_orders
  RETURNING id, amount
)
INSERT INTO order_processing_log (order_id, amount, processed_at, source)
SELECT id, amount, NOW(), 'staging_transfer'
FROM inserted;

-- Upsert с возвратом результата в CTE для дальнейшей обработки
WITH upserted_users AS (
  INSERT INTO users (email, name, source)
  VALUES
    ('alice@example.com', 'Alice', 'import'),
    ('bob@example.com', 'Bob', 'import')
  ON CONFLICT (email) DO UPDATE
    SET name = EXCLUDED.name,
        updated_at = NOW()
  RETURNING id, email, (xmax = 0) AS is_new
)
SELECT
  email,
  CASE WHEN is_new THEN 'created' ELSE 'updated' END AS action
FROM upserted_users;

-- Управление материализацией CTE
-- NOT MATERIALIZED: CTE встраивается в план, оптимизатор может использовать индексы
WITH expensive_filter AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE user_id = $1 AND status = 'completed'
)
SELECT * FROM expensive_filter WHERE amount > 1000;

-- MATERIALIZED: CTE вычисляется один раз, результат кешируется
-- Полезно когда CTE используется несколько раз в запросе
WITH order_stats AS MATERIALIZED (
  SELECT user_id, SUM(amount) AS total, COUNT(*) AS cnt
  FROM orders GROUP BY user_id
)
SELECT u.name, os.total, os.cnt
FROM users u
JOIN order_stats os ON u.id = os.user_id
WHERE os.total > 50000
UNION ALL
SELECT 'TOTAL', SUM(os.total), SUM(os.cnt)
FROM order_stats os;

Что происходит под капотом

• PostgreSQL 12+ изменил поведение CTE: по умолчанию CTE теперь NOT MATERIALIZED если он используется один раз и не рекурсивный. Это означает что планировщик может встроить CTE в основной план и использовать индексы, как для обычного подзапроса.
• Явная материализация: WITH cte AS MATERIALIZED (...) — результат сохраняется во временной структуре данных (analogous to temp table). Полезно когда CTE дорогой и используется несколько раз — без MATERIALIZED планировщик может выполнить его несколько раз.
• Рекурсивный CTE всегда MATERIALIZED — планировщик не может встроить его в основной план. Каждая итерация использует WorkTable, которая обновляется на каждом шаге рекурсии.
• EXPLAIN ANALYZE для CTE: MATERIALIZED CTE виден как CTE Scan в дереве плана. NOT MATERIALIZED CTE встроен и не виден как отдельный узел. Это полезно для диагностики производительности.
• Data-modifying CTE и snapshot isolation: все части запроса видят снимок данных на момент начала запроса. Если CTE-A удаляет строки, а CTE-B читает ту же таблицу — CTE-B видит данные до удаления CTE-A. Изменения становятся видимы только после завершения всего запроса.
• CYCLE detection (PostgreSQL 14+): CYCLE id SET is_cycle TO true DEFAULT false USING path. PostgreSQL автоматически отслеживает посещённые id и устанавливает is_cycle = true при повторном посещении. path — массив пройденных узлов. До PostgreSQL 14: ручная защита через WHERE depth < N или ARRAY проверку.
• Рекурсивный CTE vs ltree extension: ltree хранит иерархические пути как '1.5.12.8' и поддерживает мощные операторы запросов. Для статичных деревьев с частыми запросами — ltree быстрее. Для динамичных деревьев с частыми изменениями структуры — рекурсивный CTE гибче.
• Производительность рекурсии: каждая итерация = Hash Join или Nested Loop с WorkTable. Для дерева глубиной 10 и branching factor 5 — 5^10 потенциальных строк. Всегда ограничивай WHERE depth < N.

Термины урока

Мини-разбор реальной ситуации

E-commerce: страница категории показывает breadcrumbs (путь от корня), все подкатегории с количеством товаров, и рекомендуемые товары из всего поддерева. Первая реализация: 3 отдельных запроса в контроллере, рекурсивная функция в Ruby для breadcrumbs (по одному SELECT на уровень = 5 запросов), 800ms суммарно. Переписано: два рекурсивных CTE — один строит путь снизу вверх (breadcrumbs), другой строит поддерево сверху вниз (подкатегории). Финальный SELECT JOIN-ит товары из поддерева. Три запроса превратились в один — 35ms.