Краткий ответ

Это определяется не самим фактом использования CTE, а оптимизатором конкретной СУБД, её версией, свойствами запроса и иногда явными подсказками или параметрами. Оптимизатор может встроить CTE в основной запрос, чтобы протолкнуть фильтры и изменить план соединений, либо материализовать его как промежуточный результат.

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

Исторический контекст

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

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

Постановка проблемы

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

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

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

Подробное решение

Сначала СУБД рассматривает CTE как именованный логический подзапрос. Затем оптимизатор решает, можно ли безопасно и выгодно инлайнить его, то есть заменить ссылку на CTE его определением внутри внешнего запроса.

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

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

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

WITH totals AS (
    SELECT customer_id, SUM(amount) AS total
    FROM (VALUES (1, 10), (1, 20), (2, 5)) v(customer_id, amount)
    GROUP BY customer_id
)
SELECT a.customer_id, a.total, b.total
FROM totals a
JOIN totals b ON b.customer_id = a.customer_id;

Здесь CTE используется дважды. СУБД может один раз построить промежуточный результат и переиспользовать его либо встроить определение в места использования; конкретный выбор определяется оптимизатором и не следует автоматически из текста запроса.

Ситуация из практики

В отчёте рассчитывался агрегированный набор продаж за год, после чего он использовался в двух соединениях. Первый вариант — оставить CTE и позволить оптимизатору выбрать план. Плюс — читаемость и отсутствие привязки к конкретной СУБД; минус — результат мог вычисляться повторно или обрабатываться слишком большим объёмом.

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

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

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

Что кандидаты часто упускают

Гарантирует ли CTE выполнение ровно один раз?

Нет. Логическое определение CTE не является гарантией однократного физического вычисления. При нескольких ссылках СУБД может материализовать результат один раз, вычислить части независимо или встроить определение в каждое место использования.

Если однократное вычисление критично, нужно проверить документацию конкретной СУБД, доступные средства управления материализацией и фактический план выполнения. Даже принудительная материализация может иметь стоимость, превышающую экономию на повторном вычислении.

Чем отличается CTE от временной таблицы с точки зрения оптимизации?

CTE обычно существует только в рамках одного оператора и является частью его логического дерева. Временная таблица — отдельный объект с жизненным циклом, физическими данными и, в зависимости от СУБД, возможностью создавать индексы и собирать статистику.

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

Почему одинаковый результат запроса не означает одинаковую производительность CTE и производной таблицы?

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

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