Зачем нужен ключевое слово WITH (CTE) в PostgreSQL и как оно помогает с декомпозицией запросов?

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

Умеете ли вы писать читаемый SQL и знаете ли, как CTE ведёт себя в планировщике PostgreSQL. Следом почти всегда спрашивают про производительность: материализуется ли CTE и чем он отличается от подзапроса, а иногда просят написать рекурсивный запрос.

Как отвечать

WITH задаёт именованный подзапрос, который живёт в рамках одного запроса. Для декомпозиции это главное: сложный отчёт разбивается на шаги с понятными именами (filtered_orders, totals_by_client, top_clients), каждый шаг читается и отлаживается отдельно, на один CTE можно сослаться несколько раз.

Что важно именно в PostgreSQL:

• материализация: до версии 12 CTE всегда вычислялся отдельно и служил барьером оптимизации, условия из внешнего запроса внутрь не проталкивались. С 12 CTE без побочных эффектов, на который ссылаются один раз, встраивается как обычный подзапрос. Управлять можно явно: AS MATERIALIZED или AS NOT MATERIALIZED;
• рекурсия: WITH RECURSIVE для деревьев и графов (категории, оргструктура, цепочки ссылок). Стартовая часть, UNION ALL, рекурсивная часть с условием остановки;
• изменяющие CTE: INSERT, UPDATE, DELETE с RETURNING внутри WITH, например перенос строк в архив одним запросом. Все части видят один снимок данных.

Для Java-разработчика стоит добавить: такие запросы пишут нативным SQL в Spring Data (@Query(nativeQuery = true)), jOOQ поддерживает CTE в своём DSL, стандартный JPQL их не знает, а в HQL поддержка WITH появилась в Hibernate 6.2.

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

Говорят, что CTE всегда быстрее или всегда медленнее подзапроса. Без версии PostgreSQL и EXPLAIN ANALYZE это гадание.

Забывают, что у материализованного CTE нет индексов. Если он вернул миллион строк, а потом к нему делают JOIN по ключу, результат сканируется целиком, и встроенный подзапрос или временная таблица с индексом оказываются быстрее.

Пишут рекурсивный CTE без ограничения глубины на данных, где возможны циклы, и запрос не завершается. Защита: счётчик уровня в условии, массив пройденных узлов или конструкция CYCLE, которая появилась в PostgreSQL 14.