Что такое CTE
На этой странице
CTE (Common Table Expression) — именованный подзапрос, объявленный через WITH name AS (...) перед основным запросом. CTE делает сложный запрос читаемым по шагам: «отфильтровать → посчитать → соединить», позволяет сослаться на один промежуточный результат несколько раз и является основой рекурсивных запросов. В PostgreSQL 12+ обычный CTE, на который ссылаются один раз, встраивается в основной запрос (оптимизируется как подзапрос); AS MATERIALIZED принудительно вычисляет его один раз, AS NOT MATERIALIZED — наоборот. В PostgreSQL CTE может содержать и INSERT/UPDATE/DELETE … RETURNING (data-modifying CTE).
Вопрос
Что такое CTE и чем он отличается от подзапроса?
Запрос по шагам
WITH paid_orders AS (
SELECT user_id, total
FROM orders
WHERE status = 'paid'
AND created_at >= now() - interval '90 days'
),
user_revenue AS (
SELECT user_id, sum(total) AS revenue, count(*) AS orders
FROM paid_orders
GROUP BY user_id
)
SELECT u.email, r.revenue, r.orders
FROM user_revenue r
JOIN users u ON u.id = r.user_id
WHERE r.revenue > 10000
ORDER BY r.revenue DESC;Каждый CTE видит предыдущие — запрос читается сверху вниз.
Повторное использование
WITH stats AS (
SELECT avg(total) AS avg_total, stddev(total) AS sd FROM orders
)
SELECT o.*
FROM orders o, stats s
WHERE o.total > s.avg_total + 3 * s.sd; -- аномально крупные заказыMATERIALIZED и NOT MATERIALIZED (PostgreSQL 12+)
WITH recent AS MATERIALIZED (
SELECT * FROM events WHERE created_at > now() - interval '1 hour'
)
SELECT ... FROM recent r1 JOIN recent r2 ON ...;| Поведение | Когда |
|---|---|
| По умолчанию: встраивается, если не рекурсивный, без побочных эффектов и используется один раз | Обычные запросы |
MATERIALIZED | Тяжёлое вычисление используется несколько раз; нужно «оптимизационное ограждение» |
NOT MATERIALIZED | Нужно, чтобы фильтры основного запроса проникли внутрь CTE, используемого несколько раз |
До PostgreSQL 12 CTE всегда материализовался — старые советы «CTE медленнее подзапроса» относятся к этому.
Data-modifying CTE
-- перенести старые заказы в архив одной командой
WITH moved AS (
DELETE FROM orders
WHERE created_at < now() - interval '3 years'
RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;Все части выполняются в одном снимке данных, атомарно.
CTE или подзапрос
| CTE | Подзапрос | |
|---|---|---|
| Читаемость сложной логики | Высокая | Падает с вложенностью |
| Повторное использование | Да | Нужно дублировать |
| Рекурсия | WITH RECURSIVE | Нет |
| Производительность в PG 12+ | Обычно та же | — |
Частые ошибки
- Считать, что CTE всегда кешируется (в PG 12+ это не так).
MATERIALIZEDтам, где фильтр снаружи мог бы использовать индекс.- CTE-«лапша» из десяти шагов, где хватило бы одного
JOIN. - Ожидать, что
UPDATEв одном CTE виденSELECTв другом CTE того же запроса — они видят исходный снимок.
Что важно помнить
- CTE — именованный шаг запроса для читаемости и переиспользования.
- В PostgreSQL 12+ одноразовый CTE встраивается.
- CTE умеет изменять данные и быть рекурсивным.
Мини-шпаргалка
WITH a AS (...), b AS (... FROM a) SELECT ....AS MATERIALIZED/AS NOT MATERIALIZED.WITH x AS (DELETE ... RETURNING *) INSERT ....WITH RECURSIVE— иерархии.