CTE и рекурсивные запросы в PostgreSQL: что выбрать

В PostgreSQL 16 CTE помогает разбить длинный запрос на понятные этапы, а рекурсивный CTE — пройти по дереву категорий или связям между записями. Но выбор не сводится к «CTE читается лучше»: способ записи влияет на план выполнения, а рекурсия без ограничения может обойти больше данных, чем вы ожидали.

Три способа выразить одну задачу

Рассмотрим PostgreSQL 16 и таблицу категорий с полями id, parent_id и name. Нужно отобрать строки по условию, пройти по иерархии или многократно использовать промежуточный результат. Для этого есть три основных подхода: вложенный запрос, обычный CTE и рекурсивный CTE. Если иерархия большая и её регулярно читают, стоит отдельно сравнить их с таблицей замыкания — структурой, где заранее сохранены пары «предок — потомок».

Обычный CTE: сначала читаемость, потом план

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

WITH totals AS (
    SELECT customer_id, sum(amount) AS total
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, total
FROM totals
WHERE total > 1000;

Здесь нет рекурсии: запрос читает таблицу заказов, группирует суммы и фильтрует результат. Тот же расчёт можно записать как вложенный запрос. Выбирайте CTE, если он даёт этапу осмысленное имя или используется несколько раз, а не потому, что любой CTE быстрее.

В PostgreSQL 12 и новее планировщик обычно может встроить нерекурсивный CTE без побочных эффектов в основной запрос, если на него ссылаются один раз. При нескольких ссылках CTE обычно вычисляется отдельно; поведение можно уточнить словами MATERIALIZED или NOT MATERIALIZED. Первое запрещает встраивание, второе просит планировщик рассматривать CTE как часть внешнего запроса. Это не универсальные ускорители: материализация экономит повторные вычисления, но может записать большой промежуточный набор; встраивание позволяет протолкнуть фильтры, но иногда повторяет работу.

Термин — материализация: PostgreSQL вычисляет промежуточный результат и сохраняет его для последующего чтения, вместо того чтобы подставить выражение CTE в основной запрос. Это может занимать память, а при нехватке ресурсов — временное дисковое пространство.

CTE — прежде всего способ задать структуру запроса. Ускорение подтверждает план выполнения, а не сам факт, что запрос начинается с WITH.

Рекурсивный CTE: обход дерева без цикла в приложении

Рекурсивный CTE состоит из начального набора строк и шага, который присоединяет к уже найденным строкам новые. В PostgreSQL этот обход выполняется итеративно: результат шага становится входом для следующего. Ниже запрос находит категорию с заданным идентификатором и всех её потомков. Параметр $1 передаётся драйвером как значение, а не склеивается со строкой запроса.

WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, 0 AS depth, ARRAY[id] AS path
    FROM categories
    WHERE id = $1

    UNION ALL

    SELECT child.id, child.parent_id, child.name,
           tree.depth + 1, tree.path || child.id
    FROM categories AS child
    JOIN tree ON child.parent_id = tree.id
    WHERE NOT child.id = ANY(tree.path)
)
SELECT id, parent_id, name, depth
FROM tree
ORDER BY path;

Начальная часть выбирает корень, рекурсивная — его дочерние строки, затем следующий уровень. Массив path хранит уже посещённые идентификаторы на текущем пути: условие отсекает повторное посещение и не даёт циклу продолжаться бесконечно. Для обычного дерева, где у каждой записи один родитель, это предохранитель от испорченных данных. В графе узел может встретиться по разным путям; такой запрос вернёт его несколько раз, если пути не совпадают.

Почему не заменить UNION ALL на UNION

UNION удаляет дубликаты строк, а UNION ALL сохраняет их и обычно требует меньше работы. Но в рекурсивном запросе с глубиной и путём один и тот же узел на разных уровнях — уже не одинаковая строка. Поэтому простая замена на UNION не гарантирует остановку цикла. Явно задайте правило обхода: проверяйте путь, ограничивайте глубину или используйте подходящую для вашей версии конструкцию обнаружения циклов.

Когда вместо запроса нужна другая структура

Рекурсивный CTE удобен, когда приложение получает поддерево по запросу, а глубина заранее неизвестна. Если приложение делает такой обход для тысяч запросов в секунду, а связи меняются редко, стоимость повторной рекурсии может оказаться выше стоимости поддержки отдельной таблицы замыкания. В ней хранят каждую пару предок–потомок и расстояние между ними. Тогда чтение проще, но добавление, удаление или перенос узла требуют согласованно пересчитать связи.

Не переводите систему на таблицу замыкания только из-за длинного SQL. Сначала проверьте частоту обходов, размер дерева, распределение глубины и цену изменений. Если данные образуют граф с большим числом путей, рекурсивный CTE тоже может выдавать слишком много строк: ограничение по глубине не заменяет модель данных и определение того, что считать уникальным результатом.

Как выбрать и проверить

• Один простой этап и одно использование — начните с вложенного запроса или обычного CTE, выбрав более понятный вариант.
• Несколько логических этапов — используйте CTE с говорящими именами; если он тяжёлый или повторно используется, отдельно проверьте материализацию.
• Обход дерева по запросу — используйте рекурсивный CTE, предусмотрите остановку на цикле и оцените максимальный объём результата.
• Частые чтения и редкие изменения иерархии — сравните рекурсию с таблицей замыкания на данных и профиле нагрузки вашего проекта.

Проверяйте план через EXPLAIN, а фактическое время и число строк — через EXPLAIN ANALYZE на безопасном для нагрузки окружении. Сравнивайте один и тот же запрос, объём данных и параметры: смотрите, сколько строк проходит через рекурсивный узел, какие таблицы читаются и появляется ли временный ввод-вывод. Универсальных чисел, после которых CTE надо заменить, нет.

Что делать дальше

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