CTE и рекурсивные запросы: чек-лист перед продакшеном

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

Сначала выясните, что именно делает CTE

Обычный CTE с виду похож на временную таблицу, но в PostgreSQL не всегда ею становится. Начиная с версии 12 планировщик может встроить нерекурсивный CTE в основной запрос. Для побочного, свободного от побочных эффектов CTE, который используется один раз, это обычное поведение. Если CTE нужен в нескольких местах, PostgreSQL обычно вычисляет его отдельно и повторно использует результат.

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

Не ставьте MATERIALIZED «для надёжности» и не снимайте материализацию только потому, что запрос кажется медленным. Сравните планы и время на одинаковых данных. Изменение может помочь одному запросу и навредить другому.

Чек-лист для обычного CTE

1. Посмотрите, сколько раз используется CTE. Одно использование и несколько — разные ситуации для планировщика. Если результат повторно нужен нескольким частям запроса, проверьте, не дешевле ли вычислить его один раз. Если CTE читается один раз, убедитесь, что внешний фильтр не отрезан от исходной таблицы ненужной материализацией.
2. Сравните план с явной материализацией и без неё. В PostgreSQL 17 это можно проверить на копии запроса с MATERIALIZED и NOT MATERIALIZED. Смотрите не только на итоговое время: важны фактическое число строк, повторные чтения, сортировки и временные файлы. Если указание меняет план, но не улучшает время на репрезентативном наборе данных, не фиксируйте его без причины.
3. Запустите EXPLAIN (ANALYZE, BUFFERS) на безопасной среде. ANALYZE действительно выполняет запрос. На изменяющем данные запросе это может означать изменение данных, поэтому проверку проводите на копии или внутри подходящей транзакции с откатом. Сравните оценки планировщика с фактическими строками и число чтений буферов.
4. Проверьте, где применяются фильтры. Если запрос выбирает записи за короткий период, в плане не должно без причины обрабатываться всё историческое содержимое CTE. Проверьте, какие условия выполняются до соединений и агрегаций, а какие остаются снаружи. Не делайте вывод по одному только виду SQL: решает план.
5. Испытайте характерный объём и перекос данных. Маленькая тестовая таблица не покажет стоимость чтения большого результата, а равномерные данные могут скрыть тяжёлый популярный ключ. Для проверки нужны реальные порядки объёма и распределение, близкое к рабочему.

Проверьте рекурсию на цикл и рост

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

Предположим, таблица nodes хранит связи «родитель — потомок»: у узла есть id и parent_id. Пример для PostgreSQL 14+ и более новых версий ищет потомков заданного узла, ограничивает глубину и отмечает циклический путь:

WITH RECURSIVE walk(id, parent_id, depth) AS (
    SELECT id, parent_id, 0
    FROM nodes
    WHERE id = $1

    UNION ALL

    SELECT child.id, child.parent_id, walk.depth + 1
    FROM nodes AS child
    JOIN walk ON child.parent_id = walk.id
    WHERE walk.depth < $2
)
CYCLE id SET is_cycle USING path
SELECT id, parent_id, depth, path
FROM walk
WHERE NOT is_cycle;

Передайте в $1 идентификатор корня, а в $2 — ограничение глубины; оно должно быть задано и проверено приложением. Индекс на nodes(parent_id) помогает находить дочерние строки на каждом шаге. Если таблица и запрос требуют также быстро получить id, оцените составной индекс (parent_id, id) на реальном плане. Не создавайте оба индекса автоматически: каждый занимает место и увеличивает цену записи.

Что обязательно проверить до выкладки

• Условие остановки. В примере глубину ограничивает параметр. Для конкретной иерархии проверьте, что ограничение не обрезает допустимые результаты, но защищает от неожиданно глубоких данных. Ограничение глубины не заменяет обнаружение циклов.
• Повторное посещение узла. CYCLE помогает обнаружить цикл по указанному столбцу и не продолжать путь, на котором он найден. Выберите ключ, действительно идентифицирующий узел. Для старых версий PostgreSQL, где CYCLE недоступен, защиту от повторов нужно выразить иначе; сверяйте синтаксис с документацией именно вашей версии.
• Семантика UNION. Замена UNION ALL на UNION удаляет дубликаты строк результата, но не является универсальной защитой от циклов. Если в строке есть меняющаяся глубина или путь, повторное посещение узла может всё равно давать отличающиеся строки.
• Индекс рекурсивного перехода. Индекс нужен по столбцу, по которому рекурсивная часть ищет следующую порцию строк. В примере это parent_id. Проверьте, что план использует его на нескольких уровнях обхода, а не только при поиске начального узла.
• Широкие ветви и большие результаты. Маленькая глубина не гарантирует маленький результат: один уровень может содержать очень много потомков. Измерьте число возвращённых строк, время и буферы для корней с наибольшим ожидаемым количеством связей.
• Параллельные изменения данных. Если связи меняются во время обхода, результат должен соответствовать требованиям приложения к согласованности. Отдельно решите, допустим ли снимок данных на момент выполнения запроса или обход нужно согласовать с изменениями.

Не используйте внешний LIMIT как единственную аварийную остановку рекурсии: он ограничивает выдачу строк, но не всегда гарантирует, что рекурсивная часть выполнила только необходимую работу. Для контроля нужны корректная логика обхода и явные ограничения, соответствующие задаче.

Безопасный рекурсивный запрос — не тот, который «обычно быстро заканчивается», а тот, у которого известны максимальная глубина, поведение при цикле и цена самого широкого ожидаемого обхода.

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

Где решение ломается

Чаще всего проблема не в ключевом слове WITH, а в предположениях вокруг него. В production-запросе проверьте конкретные границы:

• Цикл появился после импорта или ручного изменения связей. Тестовая иерархия была деревом, а рабочие данные стали графом.
• Дерево оказалось намного шире тестового. Ограничение глубины сработало, но число строк на промежуточных уровнях выросло.
• Индекс есть на первичном ключе, но нет индекса на столбце перехода. Начальный узел находится быстро, а поиск его потомков каждый раз читает большую часть таблицы.
• CTE используется несколько раз, и его повторное вычисление стало дорогим; либо, наоборот, материализация заставила сохранить слишком много строк до применения фильтра.
• Проверку проводили только на небольших данных или без BUFFERS, поэтому не заметили масштаб чтения и временного хранения.

При расследовании сравните план и метрики с базовой версией запроса. Фиксируйте объём данных, параметры запуска, глубину обхода, число строк результата и время. Универсального порога в миллисекундах для всех схем нет: важны ваш SLO, частота вызова и конкуренция за ресурсы базы.

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

Перед выкладкой сохраните планы для типичного и худшего ожидаемого случая, затем прогоните запросы на копии рабочих данных. Для рекурсивного обхода добавьте в тесты цикл, широкую ветвь и максимальную допустимую глубину. После запуска наблюдайте за временем запроса, количеством обработанных строк и чтением буферов; если меняете версию PostgreSQL, повторите проверку плана на новой версии. Детали синтаксиса и планирования сверяйте с документацией PostgreSQL 17.