Как работают CTE и рекурсивные запросы в PostgreSQL?

Question

Accepted Answer

**CTE** (Common Table Expression), определённое с помощью **`WITH`**, — это именованное временное множество результатов, которое делает сложные запросы читаемыми. PostgreSQL полностью поддерживает CTE, включая **рекурсивные** CTE (`WITH RECURSIVE`) для обхода иерархических/древовидных данных.

## Базовые CTE — построение читаемых запросов

```sql
WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
order_counts AS (
  SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id;   -- compose named steps cleanly
```

CTE разбивает сложный запрос на именованные, читаемые этапы (связанные, каждый ссылается на предыдущие) — намного понятнее, чем вложенные подзапросы.

## Рекурсивные CTE — обход иерархий

```sql
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
  -- BASE CASE: the starting point
  SELECT id, name, manager_id FROM employees WHERE id = 1
  UNION ALL
  -- RECURSIVE STEP: find employees whose manager is in the result so far
  SELECT e.id, e.name, e.manager_id
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;          -- the entire tree under employee 1
```

**Рекурсивное CTE** содержит базовый случай (начальные строки) и рекурсивный шаг (`UNION ALL` объединение обратно с самим собой), итерирующее, пока не появятся новые строки — идеально подходит для **деревьев и иерархий** (организационные структуры, деревья категорий, спецификации изделий, пути в графе).

## Примечание о материализации CTE (специфика Postgres)

```sql
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...)      -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...)  -- force inlining (optimize into the main query)
```

До Postgres 12 CTE всегда были "optimization fence" (материализованы); теперь они могут быть встроены для лучшей производительности — `MATERIALIZED`/`NOT MATERIALIZED` контролирует это.

## Почему это важно

CTE и рекурсивные запросы — ценные возможности PostgreSQL для **читаемости и работы с иерархическими данными**, поэтому их понимание полезно для написания поддерживаемых сложных запросов и обработки древовидных данных.

Повседневная выгода — **читаемость**: сложные запросы с несколькими уровнями вложенных подзапросов становятся сложны в обслуживании, а CTE позволяют разложить их на именованные, последовательные логические этапы (читаются сверху вниз), dramатически улучшая ясность — делая CTE важным инструментом для написания поддерживаемого SQL.

Выдающаяся возможность — **рекурсивные CTE** (`WITH RECURSIVE`), которые позволяют запрашивать **иерархические и древовидные данные** (организационные диаграммы, деревья категорий, спецификации изделий, ветвлённые комментарии, обход графа) — класс запросов, иначе очень сложных в SQL, делая рекурсивные CTE стандартным решением для иерархического обхода.

Понимание структуры рекурсивного CTE (базовый случай + рекурсивный шаг, соединяющийся с самим собой) — ключевое знание для этой распространённой необходимости.

Полезная специфика Postgres — поведение **материализации CTE** (Postgres 12+ может встроить CTE для лучшей производительности, контролируемое с помощью `MATERIALIZED`/`NOT MATERIALIZED`) — релевантно для оптимизации.

Поскольку сложные запросы распространены (и читаемость важна для обслуживания), и поскольку иерархические данные (которые рекурсивные CTE хорошо обрабатывают) встречаются часто (организационные структуры, вложенные категории), понимание CTE и рекурсивных запросов — ценное, практически релевантное знание PostgreSQL, которое улучшает поддерживаемость запросов и разблокирует иерархическое запрашивание — часто релевантная возможность для чистого, сложного SQL и распространённая тема, особенно рекурсивная форма для повторяющейся необходимости запрашивать древовидные данные.