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

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

Понимаете ли вы, что CTE прежде всего инструмент читаемости, и знаете ли, как PostgreSQL его на самом деле выполняет. Главный уточняющий вопрос: материализуется ли CTE и становится ли запрос от него быстрее. Второй: что умеет WITH RECURSIVE.

Как отвечать

WITH даёт имя подзапросу, и основной запрос обращается к нему как к таблице. Сложный отчёт раскладывается на шаги сверху вниз: отобрали, агрегировали, соединили. Каждый шаг читается и отлаживается отдельно.

Дальше то, что отличает сильный ответ:

• материализация: начиная с PostgreSQL 12 CTE без побочных эффектов, на который ссылаются один раз, встраивается в основной запрос, и планировщик оптимизирует его как обычный подзапрос. Если ссылок несколько, по умолчанию он вычисляется один раз и материализуется. Поведение можно задать явно: AS MATERIALIZED или AS NOT MATERIALIZED;
• WITH RECURSIVE: обход деревьев и графов, например категорий или оргструктуры. Нужны стартовая нерекурсивная часть, UNION ALL и условие остановки, а с версии 14 есть конструкция CYCLE для защиты от циклов;
• изменяющие CTE: WITH moved AS (DELETE FROM orders WHERE ... RETURNING *) INSERT INTO archive SELECT * FROM moved переносит строки одной командой, атомарно. Все части видят один снимок данных, поэтому изменения одной части другой не видны.

Для Go-разработчика это ещё и способ сократить число обращений к базе. Вместо трёх запросов из кода с передачей промежуточных результатов через слайсы можно отправить один запрос с CTE через pgx или database/sql, а sqlc сгенерирует для такого запроса типизированный код.

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

Называют CTE временной таблицей. Это часть одного запроса, она живёт только во время его выполнения, и индексов на ней нет.

Повторяют устаревшее «CTE всегда барьер оптимизации» или обратное «CTE всегда быстрее подзапроса». Для современных версий оба утверждения неверны: всё зависит от числа ссылок и подсказки MATERIALIZED, а проверяется через EXPLAIN.

Пишут рекурсивный CTE без условия остановки или гоняют UNION ALL по графу с циклами. Запрос уходит в бесконечность, пока не упрётся в память или таймаут.