CTE (WITH)
Тема дорожной карты · PostgreSQL
Обобщённые табличные выражения (CTE), вводимые ключевым словом WITH, позволяют определять именованные временные результирующие наборы, на которые можно ссылаться несколько раз в рамках одного запроса PostgreSQL, существенно повышая читаемость и открывая возможности для рекурсивных шаблонов запросов. Простое CTE — WITH recent_orders AS (SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days') SELECT * FROM recent_orders WHERE total > 100 — позволяет планировщику реляционной базы данных либо встроить подзапрос, либо материализовать его в зависимости от оценок стоимости PostgreSQL и подсказок MATERIALIZED / NOT MATERIALIZED. Рекурсивные CTE, записываемые как WITH RECURSIVE, являются стандартным подходом PostgreSQL для обхода иерархических структур данных или графов, таких как организационные схемы, деревья категорий и таблицы спецификаций материалов. Начиная с PostgreSQL 12, нерекурсивные CTE по умолчанию работают в режиме NOT MATERIALIZED, что позволяет планировщику включать их во внешний запрос для лучшей оптимизации, тогда как WITH ... AS MATERIALIZED принудительно выполняет однократное вычисление и может служить барьером оптимизации для сложных аналитических запросов.
Как это работает
CTE (WITH) соединяет данные между таблицами: INNER, LEFT/RIGHT/FULL OUTER, CROSS, LATERAL (per-row подзапрос), SELF (alias той же таблицы). View — сохранённые запросы (CREATE VIEW v AS SELECT ...); материализованные view (CREATE MATERIALIZED VIEW ...) кешируют результат на диск, требуют REFRESH. Оконные функции (OVER (PARTITION BY ... ORDER BY ...)) считают per-row агрегацию без коллапса строк. CTE (WITH ... AS) именуют подзапросы для читаемости.
Когда применять
View — для энкапсуляции сложных запросов, переиспользуемых вызывающими: версионированная API-поверхность над схемой. Материализованные view — когда подлежащий запрос дорогой, staleness секунд-часов допустим. LATERAL — когда подзапросу нужны колонки внешнего (top-N per group — канонический пример). Оконные функции вместо сложных self-join — для running totals, рангов, перцентилей.
Типичные ошибки
Ловушки CTE (WITH): глубоко вложенные view (SELECT из view, join-ящего три view — планировщик видит один большой запрос, может выбрать плохой план); никогда не рефрешат materialized view (stale данные неделями); FULL OUTER JOIN, где надо INNER (NULL-потоп); cartesian-взрыв от пропущенного JOIN-условия (5 таблиц × 5 unrelated строк = миллионы). Всегда EXPLAIN для join перед деплоем.