Общие табличные выражения (CTE) в SQL: оптимизация и читаемость сложных запросов

Общие табличные выражения (CTE) в SQL: оптимизация и читаемость сложных запросов

По мере роста бизнес-логики SQL-запросы неизбежно усложняются. Вложенные подзапросы, многократные соединения одних и тех же таблиц, нагромождение фильтрации — всё это превращает код в трудночитаемый массив, который сложно отлаживать. Общие табличные выражения (Common Table Expressions, или CTE), задаваемые с помощью ключевого слова WITH, позволяют структурировать сложные SQL-запросы, делая их линейными и понятными.

В этой статье мы разберем, как работают CTE, в чем их отличие от подзапросов и временных таблиц, как с их помощью обходить древовидные структуры и как управлять производительностью базы данных с помощью директив материализации.

Анатомия WITH-запросов и их практическая ценность

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

Базовый синтаксис нерекурсивного CTE:

WITH user_purchase_summary AS (
    SELECT 
        user_id,
        COUNT(order_id) AS total_orders,
        SUM(amount) AS total_spent
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
)
SELECT 
    u.id,
    u.email,
    ps.total_orders,
    ps.total_spent
FROM users u
LEFT JOIN user_purchase_summary ps ON u.id = ps.user_id
WHERE ps.total_spent > 1000;

Главная ценность нерекурсивных CTE заключается в декомпозиции логики. Вместо написания громоздких JOIN с вложенными SELECT, разработчик может последовательно создать несколько именованных блоков (например, один для агрегации продаж по клиентам, второй — для лучших продуктов) и затем объединить их. Это значительно упрощает отладку: отдельный именованный блок можно временно выполнить как самостоятельный SELECT и проверить строки до подключения к финальному запросу.

Сравнение: CTE, вложенные подзапросы и временные таблицы

Для промежуточного хранения данных в SQL применяются три подхода. Каждый из них имеет свои особенности.

Вложенные подзапросы

Подзапросы пишутся внутри тела основного запроса. Главный их недостаток — плохая читаемость (их приходится читать изнутри наружу). Кроме того, если один результат вычислений требуется использовать в нескольких местах запроса, подзапрос приходится дублировать, увеличивая нагрузку на СУБД.

Общие табличные выражения

CTE решают проблему читаемости благодаря подходу сверху вниз. Результат одного CTE можно использовать в последующих выражениях того же блока WITH или несколько раз сослаться на него в финальном запросе. Время жизни данных ограничено выполнением запроса.

Временные таблицы

Временные таблицы создаются физически (например, через CREATE TEMPORARY TABLE) и сохраняют состояние на протяжении всей сессии подключения к БД. Они поддерживают создание собственных индексов и сбор статистики, но требуют явного удаления и изменяют состояние сессии.

Рекурсивные CTE: обход иерархий и графов

С помощью конструкции WITH RECURSIVE можно выполнять рекурсивные запросы. Это незаменимый инструмент для работы с древовидными структурами данных: каталогами категорий или графами зависимостей.

Рекурсивный CTE состоит из трех элементов:

1. Базовый запрос (Anchor Member): нерекурсивная часть, возвращающая начальные строки (корень дерева).
2. Рекурсивный запрос (Recursive Member): часть запроса, которая соединяет таблицу с самим CTE и выполняет пошаговое вычисление, ссылаясь на результаты предыдущего шага.
3. Условие остановки: рекурсия прекращается, когда очередной шаг возвращает пустой набор данных. Соединение выполняется через UNION или UNION ALL.

Пример рекурсивного обхода категорий товаров для построения пути:

WITH RECURSIVE category_path AS (
    SELECT id, parent_id, name, name AS path
    FROM categories
    WHERE id = 42
    UNION ALL
    SELECT c.id, c.parent_id, c.name, CONCAT(c.name, ' -> ', cp.path) AS path
    FROM categories c
    JOIN category_path cp ON c.id = cp.parent_id
)
SELECT id, name, path FROM category_path WHERE parent_id IS NULL;

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

Внутренняя механика выполнения и оптимизация

Перенос подзапросов в CTE не гарантирует автоматического ускорения. Влияние CTE на производительность зависит от того, как планировщик СУБД обрабатывает эти выражения.

Существует два подхода к выполнению CTE:

• Встраивание (Inlining / Not Materialized): планировщик рассматривает CTE как обычный подзапрос и оптимизирует весь запрос целиком. В этом случае фильтры из внешнего запроса могут проникать внутрь выражений CTE (filter pushdown), позволяя СУБД использовать индексы.
• Материализация (Materialization): база данных вычисляет результат CTE один раз во временную таблицу в памяти, а затем обращается к ней. Это предотвращает повторные вычисления, но создает оптимизационный барьер: планировщик не может оптимизировать внутренний запрос с учетом внешних фильтров.

В PostgreSQL до версии 12 все CTE материализовались принудительно. Начиная с версии 12, планировщик встраивает CTE в родительский запрос автоматически, если выражение используется один раз. Для явного управления поведением используются директивы MATERIALIZED или NOT MATERIALIZED.

Пример использования NOT MATERIALIZED

Если планировщик PostgreSQL материализовал CTE и перестал использовать индексы на большой таблице, можно встроить его:

WITH large_data AS NOT MATERIALIZED (
    SELECT id, payload, status FROM documents WHERE created_at > '2026-01-01'
)
SELECT * FROM large_data WHERE status = 'critical';

Пример использования MATERIALIZED

Если один и тот же тяжелый расчет используется в финальном запросе несколько раз, материализация предотвратит повторные вычисления:

WITH heavy_calculation AS MATERIALIZED (
    SELECT department_id, AVG(salary) AS avg_sal FROM employees GROUP BY department_id
)
SELECT * FROM heavy_calculation hc1 JOIN heavy_calculation hc2 ON hc1.department_id = hc2.department_id;

Чек-лист по диагностике и оптимизации запросов с CTE

Чтобы использование CTE приносило пользу производительности, придерживайтесь регламента проверки:

• Проверьте исходный план запроса. Перед любыми изменениями выполните исходный запрос с помощью команды EXPLAIN ANALYZE, чтобы зафиксировать базовое время выполнения.
• Оцените количество использований каждого CTE. Если табличное выражение вызывается ровно один раз, убедитесь, что планировщик встроил его (в плане не должно быть узла CTE Scan). Если произошло нежелательное кэширование, добавьте NOT MATERIALIZED.
• Проверьте глубину рекурсии. При написании рекурсивных CTE убедитесь, что в исходной таблице отсутствуют циклические ссылки. Настройте лимиты на количество строк, если данные поступают из внешних источников.
• Проанализируйте влияние оптимизационных барьеров. Если в плане запроса после внедрения WITH появились операции Sequential Scan на больших таблицах вместо Index Scan, это признак заблокированного прохода фильтров. Замените CTE на JOIN или примените NOT MATERIALIZED.
• Избегайте перегрузки одного WITH-блока. Не объединяйте в одном объявлении более 5–7 не связанных логически блоков CTE. Это затрудняет чтение и усложняет построение оптимального плана выполнения.

Заключение

Общие табличные выражения (CTE) представляют собой отличный инструмент для борьбы со сложностью кода. Они превращают вложенные подзапросы в читаемые, последовательные цепочки шагов. Однако понимание механики материализации и регулярная проверка планов выполнения через EXPLAIN необходимы для написания производительного SQL-кода.