Временная таблица vs CTE: разница

Зачем это различать

На собеседовании middle-аналитика популярный вопрос: «в чём разница между temporary table и CTE?». Правильный ответ отличает middle от junior. Оба — инструменты для промежуточных результатов, но ведут себя совершенно по-разному.

CTE (Common Table Expression, WITH) — inline в одном запросе, живёт секунды. Temp table — реальный объект в базе на всю сессию, можно индексировать и переиспользовать. Выбор влияет на читаемость, производительность и на то, сможете ли вы использовать результат в нескольких запросах подряд.

В ad-hoc работе аналитик часто начинает с CTE (проще), потом при необходимости переносит в temp table (быстрее, можно индексировать). На больших данных это критично: CTE с 100M строк может выполняться минуты, temp table с индексом — секунды.

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

• CTE (WITH) — inline, в одном запросе, без физического хранения
• Temp table (CREATE TEMP TABLE) — реальная таблица, живёт на время сессии

Сравнение

	CTE	Temp table
Область видимости	один запрос	вся сессия
Физическое хранение	нет (в Postgres 12+ inline)	да
Индексы	нет	можно
Статистика планировщика	нет	есть
Переиспользование	только в том же SELECT	много раз
Синтаксис	WITH name AS (...)	CREATE TEMP TABLE name AS ...
Cleanup	автомат	автомат на конец сессии

Пример CTE

WITH active_users AS (
    SELECT user_id FROM users WHERE last_login > NOW() - INTERVAL '30 days'
),
user_revenue AS (
    SELECT user_id, SUM(total) AS revenue
    FROM orders WHERE user_id IN (SELECT user_id FROM active_users)
    GROUP BY user_id
)
SELECT * FROM user_revenue WHERE revenue > 10000;

Всё в одном SELECT. После выполнения данные исчезают.

Пример temp table

-- создаём
CREATE TEMP TABLE active_users AS
SELECT user_id FROM users WHERE last_login > NOW() - INTERVAL '30 days';

-- опционально — индекс
CREATE INDEX idx_active ON active_users(user_id);

-- можем переиспользовать в других запросах
SELECT * FROM active_users WHERE ...;
SELECT u.*, r.revenue FROM active_users u JOIN revenue r ON ...;

-- автоматически удалится в конце сессии

Когда CTE

1. Промежуточный результат нужен в одном запросе
2. Данные небольшие (до миллиона строк)
3. Нужна читаемость — CTE WITH step1 AS (...) читаемее подзапросов
4. Рекурсия (CTE умеет, temp table нет)
5. Быстрый прототип / ad-hoc

Когда temp table

1. Результат нужен в нескольких запросах подряд
2. Данные большие (миллионы+)
3. Нужен индекс для ускорения последующих запросов
4. Сложная логика разбита на шаги (ETL-like)
5. Хочется проверять промежуточные результаты
6. Планировщик даёт плохой план на CTE

Пример когда temp table быстрее

-- CTE — повторно сканирует src для каждого использования
WITH big_cte AS (
    SELECT ... FROM huge_table WHERE complex_condition
)
SELECT *
FROM big_cte a
JOIN big_cte b ON a.id = b.parent_id  -- big_cte вычисляется 2 раза
WHERE ...;

-- temp table — один раз, с индексом
CREATE TEMP TABLE filtered AS
SELECT ... FROM huge_table WHERE complex_condition;

CREATE INDEX ON filtered(id);
CREATE INDEX ON filtered(parent_id);

SELECT *
FROM filtered a
JOIN filtered b ON a.id = b.parent_id
WHERE ...;

На 100M строк разница может быть в десятки раз.

Postgres 12+ специфика

До Postgres 12 — CTE были всегда materialized (один раз вычислены, сохранены в памяти). С 12+ — inline по умолчанию (оптимизатор видит полный запрос).

Для явного materialize:

WITH cte AS MATERIALIZED (
    SELECT ...
)

Иногда материализация ускоряет (избегаем повторного вычисления). Иногда замедляет (оптимизатор не видит всё).

Подзапрос vs CTE vs temp table

Три уровня абстракции:

-- 1. подзапрос — для одноразового использования
SELECT * FROM (SELECT ... ) t WHERE ...;

-- 2. CTE — для читаемости и/или переиспользования в том же запросе
WITH t AS (SELECT ...) SELECT * FROM t WHERE ...;

-- 3. temp table — для тяжёлых промежуточных + reuse
CREATE TEMP TABLE t AS SELECT ...;
SELECT * FROM t WHERE ...;

Каждое имеет место.

Recursive — только CTE

Temp table не умеет рекурсию. Рекурсивные CTE критичны для иерархий:

WITH RECURSIVE emp_hierarchy AS (
    SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN emp_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM emp_hierarchy;

CREATE TABLE AS SELECT (CTAS) — не-temp

Ещё один вариант — обычный CREATE TABLE AS. Как temp, но персистентный:

CREATE TABLE my_snapshot AS
SELECT * FROM expensive_query;

-- живёт после сессии
-- можно использовать в других соединениях

Полезно для dashboard-snapshots или кэширования.

На собесе

«Что быстрее?» Зависит. CTE в Postgres 12+ inline → оптимизатор помогает. Temp table с индексом на большом reuse быстрее.

«Что выбираете по умолчанию?» CTE для читаемости. Temp table — когда CTE медленный или нужен reuse.

«Рекурсия?» Только CTE.

«Материализовать CTE?» По необходимости. Обычно нет, но в Postgres 12+ AS MATERIALIZED форсит.

Частые ошибки

1. CTE для huge data + reuse

Медленно. Temp table лучше.

2. Temp table для one-time query

Оверхед на CREATE. CTE чище.

3. Не cleanup после long-running session

Temp tables копятся → память. В долгих сессиях делайте DROP TABLE сами.

4. Forget indexes на temp table

Создали, и не проиндексировали → медленно. Индекс — основная причина предпочитать temp.

Связанные темы

• CTE SQL — шпаргалка
• CTE vs подзапрос
• CTE vs временная таблица
• Задачи на CTE

FAQ

Когда CTE быстрее temp table?

В Postgres 12+ для small data + simple reuse. Оптимизатор inline-ит.

Temp table в одной транзакции?

Да, обычно ON COMMIT DELETE ROWS.

VIEW как альтернатива?

VIEW — постоянная структура. Не подходит для сессионных промежуточных.

В ClickHouse / BigQuery?

BigQuery: CTE + CREATE TEMP TABLE (synchronize with session). ClickHouse: CTE + обычные таблицы в in-memory engine.

---

Тренируйте SQL — откройте тренажёр с 1500+ вопросами для собесов.