SQLCTEвременные таблицыоптимизация

Временные таблицы vs CTE в SQL: что выбрать и когда

2026-07-11 11 мин

Короткий ответ: если промежуточный результат нужен один раз и запрос хочется сделать читаемым — берите CTE. Если один и тот же тяжёлый кусок используется несколько раз, по нему нужны индексы или он пересчитывается на каждом обращении — материализуйте его во временную таблицу. Подзапрос оставьте для мелочи, которую не жалко встроить прямо в WHERE или FROM. Всё остальное — нюансы диалекта, и именно из-за них один и тот же WITH летает в одной СУБД и еле ползёт в другой.

Я разбираю сотни чужих запросов на код-ревью, и почти каждый второй «тормозящий отчёт» — это красивый многоэтажный CTE, который движок честно пересчитывает пять раз. Давайте разложим, когда что реально выгодно, без религиозных войн «CTE против temp».

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

Это три способа назвать промежуточный результат, но живут они по-разному.

Подзапрос — безымянный (или с алиасом) запрос внутри другого. Существует только в момент выполнения родительского запроса, движок сам решает, как его выполнить.

SELECT city, cnt
FROM (
    SELECT city, COUNT(*) AS cnt
    FROM orders
    WHERE status = 'paid'
    GROUP BY city
) t
WHERE cnt > 100;

CTE (Common Table Expression) — тот же подзапрос, но вынесенный в блок WITH перед основным запросом и получивший имя. Читается сверху вниз, как pipeline.

WITH paid_by_city AS (
    SELECT city, COUNT(*) AS cnt
    FROM orders
    WHERE status = 'paid'
    GROUP BY city
)
SELECT city, cnt
FROM paid_by_city
WHERE cnt > 100;

Временная таблица — реальная таблица, которая физически создаётся в базе, живёт до конца сессии (или транзакции) и видна только вам.

CREATE TEMP TABLE paid_by_city AS
SELECT city, COUNT(*) AS cnt
FROM orders
WHERE status = 'paid'
GROUP BY city;

SELECT * FROM paid_by_city WHERE cnt > 100;

Ключевая разница: подзапрос и CTE — это описание «как посчитать», а временная таблица — уже посчитанный и записанный на диск (или в память) результат. Первые два движок волен переписать и оптимизировать вместе с остальным запросом. Временную таблицу он оптимизировать не может — она уже застыла.

Почему CTE иногда работает медленнее, чем кажется?

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

Смотрите на классическую ловушку:

WITH heavy AS (
    SELECT user_id, SUM(amount) AS revenue
    FROM payments
    WHERE created_at >= '2026-01-01'
    GROUP BY user_id
)
SELECT h1.user_id, h1.revenue
FROM heavy h1
JOIN heavy h2 ON h2.user_id = h1.user_id
WHERE h2.revenue > 10000;

Я сослался на heavy дважды. В PostgreSQL до 12-й версии этот CTE был бы посчитан один раз и закэширован (так называемый optimization fence). А вот в новых версиях Postgres, в ClickHouse, в SQLite CTE обычно инлайнится — то есть агрегация по payments выполнится дважды. Если таблица платежей на десятки миллионов строк, вы честно прочитали её два раза вместо одного.

Разработчик смотрит на запрос и думает: «я же вынес тяжёлое в один блок». А движок читает это как «подставь этот SELECT в оба места». Отсюда и «CTE тормозит» — хотя тормозит не CTE, а его пересчёт.

Проверить это просто: посмотрите план выполнения (EXPLAIN в Postgres, EXPLAIN в ClickHouse). Если один и тот же скан или агрегация встречается в плане несколько раз — CTE не материализуется, и это ваш кандидат на переезд во временную таблицу. Потренироваться читать такие запросы можно в SQL-тренажёре — там задачи на реальных датасетах e-commerce и fintech.

Когда выбрать временную таблицу вместо CTE?

Есть четыре чётких сигнала. Как только совпадает хотя бы один — задумайтесь о TEMP TABLE.

Первое — переиспользование. Промежуточный результат нужен в двух-трёх местах запроса или в нескольких последовательных запросах отчёта. Материализуете один раз — читаете сколько угодно.

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

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

CREATE TEMP TABLE active AS
SELECT user_id, COUNT(*) AS events_cnt
FROM events
WHERE ts >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id;

CREATE INDEX idx_active_user ON active(user_id);

-- дальше десяток джойнов к active по user_id — все по индексу

Четвёртое — очень длинный отчёт. Когда логика разрослась в монстра из 12 CTE, разбить его на 3-4 временные таблицы часто и быстрее, и понятнее: каждый шаг можно проверить отдельно через SELECT *, а не гонять весь запрос целиком.

Обратная сторона: временная таблица — это накладные расходы на создание, запись, сбор статистики. На маленьком промежутке (тысячи строк) вы этими расходами всё съедите, и CTE окажется быстрее. Не материализуйте то, что и так летает.

Материализуется ли CTE в вашей СУБД?

Это главный вопрос, и ответ зависит от диалекта. Одинаковый WITH ведёт себя по-разному, и знать это надо до того, как вы напишете отчёт на проде.

Мораль: не переносите вслепую запрос из одной базы в другую и не ждите одинаковой скорости. Тот самый двойной JOIN heavy из примера выше в старом Postgres отработает один раз, а в ClickHouse — дважды. Если хотите гарантированную материализацию независимо от диалекта — временная таблица даёт её всегда. Подтянуть теорию по синтаксису CTE в разных СУБД удобно в SQL-справочнике.

Как переписать тяжёлый CTE во временную таблицу?

Механически, за три шага. Возьмём отчёт, который считает retention и по дороге дважды дёргает дорогой промежуток.

Было — один многоэтажный запрос:

WITH cohort AS (
    SELECT user_id, DATE_TRUNC('week', MIN(created_at)) AS cohort_week
    FROM orders
    GROUP BY user_id
),
weekly AS (
    SELECT c.cohort_week, DATE_TRUNC('week', o.created_at) AS active_week,
           COUNT(DISTINCT o.user_id) AS users
    FROM cohort c
    JOIN orders o ON o.user_id = c.user_id
    GROUP BY 1, 2
)
SELECT cohort_week, active_week, users
FROM weekly
ORDER BY 1, 2;

Стало — материализуем когорты один раз и вешаем индекс:

CREATE TEMP TABLE cohort AS
SELECT user_id, DATE_TRUNC('week', MIN(created_at)) AS cohort_week
FROM orders
GROUP BY user_id;

CREATE INDEX idx_cohort_user ON cohort(user_id);

SELECT c.cohort_week,
       DATE_TRUNC('week', o.created_at) AS active_week,
       COUNT(DISTINCT o.user_id) AS users
FROM cohort c
JOIN orders o ON o.user_id = c.user_id
GROUP BY 1, 2
ORDER BY 1, 2;

Три шага всегда одни и те же: находите переиспользуемый или тяжёлый CTE, выносите его в CREATE TEMP TABLE ... AS, вешаете индекс по колонкам, которые дальше участвуют в джойнах и фильтрах. Когортный анализ и воронки — как раз тот случай, где такой рефакторинг даёт кратный выигрыш; разбор подобных задач есть в разделе кейсов.

А что с подзапросами — они вообще нужны?

Да, и не надо их стыдиться. Подзапрос — самый лёгкий инструмент из трёх, у него нет накладных расходов на именование и материализацию.

Скалярный подзапрос в SELECT, фильтр через IN или EXISTS, короткая обёртка в FROM — всё это чище решается подзапросом, чем CTE. Не выносите в WITH то, что используется ровно один раз и умещается в три строки: имя блока тут добавляет чтения, а не убирает.

SELECT o.id, o.amount
FROM orders o
WHERE o.user_id IN (
    SELECT user_id FROM users WHERE plan = 'pro'
)
AND o.status = 'paid';

Практическое правило по читаемости: один короткий промежуток — подзапрос; два-три логических шага, которые хочется назвать, — CTE; тяжёлый или переиспользуемый результат — временная таблица. EXISTS при этом почти всегда предпочтительнее IN с подзапросом на больших объёмах — движку проще остановиться на первом совпадении.

Как выбрать на практике: короткий чек-лист

Когда сомневаюсь, прохожу по вопросам сверху вниз и останавливаюсь на первом «да»:

И отдельно про рекурсию: иерархии, деревья категорий, графы обхода — это территория рекурсивного CTE (WITH RECURSIVE), временная таблица тут не поможет без цикла в приложении.

Какие ошибки я чаще всего вижу на ревью?

Три штуки повторяются из отчёта в отчёт.

Первая — CTE-небоскрёб на 15 блоков, где половина используется один раз. Такой запрос тяжело читать и невозможно оптимизировать частями. Лечится разбивкой на временные таблицы по смысловым этапам.

Вторая — вера, что CTE кэшируется. Человек ссылается на тяжёлый WITH трижды и удивляется, почему запрос в три раза медленнее ожидания. Проверьте план — почти наверняка CTE инлайнится.

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

И последнее, житейское: временные таблицы отлично живут в скриптах, дашбордных пайплайнах и dbt-моделях, но плохо ложатся в один аналитический SELECT, который вы отдаёте в BI-инструмент как единый запрос. Там правит CTE. Выбирайте инструмент под контекст исполнения, а не по привычке.

Хотите закрепить это руками, а не теорией — соберите разницу на живых данных: решайте SQL-задачи в тренажёре с настоящим PostgreSQL в браузере, пройдите базовый курс по SQL, а перед собеседованием прогоните себя по реальным вопросам работодателей. Pro открывает все 425 SQL-задач, кейсы и безлимитные AI-собеседования — но начать можно бесплатно, пяти задач хватит, чтобы почувствовать, где CTE ломается, а TEMP TABLE выручает.

Отработай SQL на практике
545 SQL-задач с автопроверкой в браузере — первые открыты без регистрации.
SQL-тренажёр →

Похожие статьи