В чем удобство табличных выражений (CTE)?

Что проверяют

Пишете ли вы SQL руками, а не только через ORM, и понимаете ли, что CTE прежде всего инструмент читаемости, а не ускоритель. Следом обычно просят написать рекурсивный запрос по иерархии и спрашивают, материализует ли PostgreSQL выражение из WITH.

Как отвечать

CTE это именованный подзапрос, который живёт в рамках одного оператора и объявляется через WITH. Временной таблицей он не является. Удобен тем, что:

• разбивает запрос на шаги: вместо трёх уровней вложенности запрос читается сверху вниз, как конвейер, и каждый шаг можно отладить отдельно;
• переиспользуется: на одно выражение можно сослаться несколько раз в основном запросе;
• умеет рекурсию: WITH RECURSIVE из якорной части и рекурсивной через UNION ALL обходит деревья категорий, оргструктуру, графы. Условие остановки обязательно, а в PostgreSQL 14 появилась конструкция CYCLE для защиты от циклов;
• изменяет данные: в PostgreSQL внутри WITH можно выполнить DELETE ... RETURNING и тут же вставить удалённые строки в архив одним оператором.

Про производительность отвечайте аккуратно. До PostgreSQL 12 CTE всегда материализовался и был барьером для оптимизатора: фильтр из внешнего запроса внутрь не проталкивался. С 12-й версии нерекурсивный CTE без побочных эффектов, на который ссылаются один раз, встраивается в запрос, а поведение можно задать явно через MATERIALIZED и NOT MATERIALIZED. Проверяется это в EXPLAIN ANALYZE.

В Python-стеке: в SQLAlchemy есть select(...).cte("name") и параметр recursive=True, а в Django ORM такие запросы обычно пишут через raw() или пакет django-cte.

Где спотыкаются

Утверждают, что CTE ускоряет запрос. Сам по себе он ничего не ускоряет, а на старых версиях PostgreSQL мог и замедлить, отрезав оптимизатору доступ к условиям снаружи.

Пишут рекурсивный CTE без условия остановки или берут UNION вместо UNION ALL, не понимая разницы. UNION убирает дубли и спасает от простых циклов, но стоит дороже.

Путают область видимости: пытаются обратиться к CTE из следующего запроса, как к временной таблице, и получают ошибку «relation does not exist».