Общие табличные выражения и рекурсивные запросы с WITH

Что это такое

WITH name AS (SELECT ...) задаёт временный именованный результат для основного запроса; оператор может определять несколько таких результатов, причём каждый может ссылаться на предыдущие. Документация PostgreSQL описывает рекурсивную форму: WITH RECURSIVE t AS (non-recursive term UNION [ALL] recursive term) вычисляет нерекурсивную часть, помещает её строки в рабочую таблицу, затем многократно вычисляет рекурсивную часть, подставляя рабочую таблицу вместо ссылки на себя, пока строки не перестанут возвращаться. UNION удаляет дубликаты строк, а UNION ALL сохраняет их. Та же страница документирует SEARCH DEPTH FIRST | BREADTH FIRST BY ... SET col для упорядочения вывода и CYCLE col SET is_cycle USING path для остановки на циклах. Там также указано, что нерекурсивный WITH-запрос без побочных эффектов, на который ссылаются один раз, по умолчанию встраивается в родительский запрос; MATERIALIZED принудительно задаёт отдельное вычисление, а NOT MATERIALIZED — встраивание.

Почему это важно

Иерархии — категории, организационные структуры, ветки комментариев, развёртки состава изделий — не укладываются в фиксированное число соединений. Рекурсивное CTE обрабатывает произвольную глубину одним оператором; нерекурсивные CTE разбивают длинный запрос на именованные шаги, которые можно читать и проверять по отдельности.

Как применять

• Цепочка предков: начните с узла (SELECT id, parent_id, 1 AS depth FROM nodes WHERE id = $1) и соединяйте рекурсивную часть по parent_id; для потомков направление обратное. Передавайте depth и ограничивайте глубину (WHERE depth < 50) для защиты от неожиданных циклов.
• Для графов, в которых могут быть циклы, используйте UNION или предложение CYCLE; UNION ALL на цикле без ограничения никогда не завершится.
• Используйте SEARCH DEPTH FIRST BY name SET ord вместе с ORDER BY ord, чтобы вывести дерево во вложенном порядке.
• CTE, изменяющие данные, перемещают строки одним оператором: WITH moved AS (DELETE FROM live WHERE ... RETURNING *) INSERT INTO archive SELECT * FROM moved.
• CTE, на которое ссылаются несколько раз, по умолчанию материализуется. Указывайте MATERIALIZED для однократно используемого CTE только для принудительного отдельного вычисления, например чтобы дорогая функция вычислялась один раз; NOT MATERIALIZED указывайте для многократно используемого CTE, если каждому использованию нужен лишь небольшой фрагмент, хорошо обрабатываемый индексом, как в примере big_table из документации.

Подводные камни

Материализованное CTE вычисляется в записанном виде, поэтому внешний WHERE нельзя протолкнуть внутрь него, а индексы базовой таблицы не обслуживают внешний предикат. Каждая строка, созданная рекурсивной частью, остаётся в результате, поэтому широкие строки в больших деревьях обходятся дорого. Документация называет вывод в порядке обхода в ширину деталью реализации, на которую нельзя полагаться, а порядок внутри уровня — неопределённым; используйте SEARCH или явный ORDER BY.