Три способа хранить промежуточный результат

В реальных запросах нам постоянно нужно «вычислить нечто, потом использовать в нескольких местах». PostgreSQL даёт три способа сделать это:

1. Подзапрос в FROM — ... FROM (SELECT ...) AS sub. Сахар, видимый только в этом запросе.
2. CTE — WITH name AS (SELECT ...) .... То же самое, но с именем и областью видимости на весь оператор.
3. Временная таблица — CREATE TEMP TABLE name AS .... Существует на всё подключение или транзакцию.

Между ними нет «лучшего» — у каждого свой trade-off. Главное — понимать, где проходит граница оптимизации для каждого варианта.

Подзапрос в FROM — нулевая абстракция

Подзапрос в FROM — это самая простая форма. Планировщик его буквально разбирает и встраивает в окружающий запрос. Никаких границ оптимизации. Любая статистика, любые push-down — всё работает прозрачно.

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

CTE до PostgreSQL 12 — optimization fence

Долгое время (примерно с 2009 до 2019) у CTE в PostgreSQL была репутация «барьера оптимизации». Это правда: до версии 12 любой CTE всегда материализовался — вычислялся в полное промежуточное отношение и складывался во временное хранилище. Только потом из этого хранилища читался внешний запрос.

Последствия:

• Push-down фильтров не работал через границу CTE. Если ты в CTE возвращаешь миллион строк, а во внешнем WHERE фильтруешь до сотни, СУБД честно вычислит миллион, материализует, и только потом отсечёт лишнее.
• Это иногда использовали намеренно, чтобы вынудить план: «материализуй здесь, не нарушай мой пайплайн».

С версии 12 (релиз октября 2019) поведение изменилось.

PostgreSQL 12+ — CTE inлайнятся по умолчанию

В современных версиях оптимизатор смотрит на CTE и решает:

• Inлайнить (как обычный подзапрос) — если CTE используется ровно один раз и не модифицирует данные.
• Материализовать — если CTE используется два и более раз, либо если он рекурсивный (WITH RECURSIVE), либо если автор написал MATERIALIZED явно.

Это значит, что в 95% случаев CTE теперь даёт тот же план, что и подзапрос в FROM. Никакой потери производительности от вынесения логики наверх — пиши то, что читаемее.

MATERIALIZED — снова поставить барьер

Если по каким-то причинам тебе нужен старый behavior — материализация даже при одном использовании — это пишется явно:

WITH expensive AS MATERIALIZED (
  SELECT ...
)
SELECT * FROM expensive ...;

Когда это полезно:

1. CTE делает дорогое вычисление (агрегацию по миллиону строк), и ты хочешь, чтобы оно произошло один раз и не повторялось.
2. CTE возвращает результат с использованием функции с побочными эффектами (nextval, например), и ты хочешь, чтобы она вызывалась ровно столько раз, сколько строк в CTE — не больше.
3. Ты хочешь точно знать план запроса и не позволять оптимизатору перебирать варианты.

Обратное — NOT MATERIALIZED — заставляет inлайнить даже при многократном использовании. Полезно, когда ты лучше планировщика знаешь, что повторное вычисление будет дёшево.

Запусти и сравни. В первом случае оптимизатор может протолкнуть WHERE total > ... (если это не запретит HAVING или агрегат) — план короче. Во втором плане ты увидишь CTE Scan поверх материализованного результата — фильтр снаружи отрабатывает уже после.

Когда нужна временная таблица

Временная таблица — это физическое промежуточное хранилище. Она пишется в pg_temp schema, живёт до конца сессии (или транзакции, если ON COMMIT DROP), и имеет настоящую статистику для оптимизатора.

Используй TEMP TABLE, когда:

• Один и тот же промежуточный результат нужен в нескольких отдельных запросах в рамках одной транзакции или сессии. CTE так не умеет — он живёт ровно один оператор.
• Промежуточный результат очень велик, и ты хочешь, чтобы планировщик имел по нему точную статистику. Особенно полезно перед сложными JOIN, где cost-based optimizer без статистики «угадывает».
• Тебе нужен индекс на промежуточном результате. CTE — нет, временная таблица — да.

CREATE TEMP TABLE recent_orders AS
  SELECT * FROM orders WHERE placed_at >= NOW() - INTERVAL '30 days';

CREATE INDEX ON recent_orders (customer_id);
ANALYZE recent_orders;

-- теперь можно делать много запросов к recent_orders с хорошим планом

В нашей вселенной мы не будем создавать индексы (это уже модуль 13), но запомни: TEMP TABLE — это тяжёлая артиллерия, к которой стоит обращаться, когда обычных CTE и подзапросов не хватает.

Сводная таблица — как выбирать

Простой алгоритм, который работает в 95% случаев:

1. Логика используется один раз, читается легко в одном запросе — пиши подзапросом в FROM или в WHERE.
2. Логика используется один раз, но запрос становится трудночитаем — выноси в CTE ради читаемости. План не пострадает.
3. Логика используется несколько раз в рамках одного оператора — выноси в CTE, оптимизатор сам материализует.
4. Логика тяжёлая, и ты хочешь гарантировать одно вычисление — WITH name AS MATERIALIZED (...).
5. Промежуточный результат нужен в нескольких отдельных запросах — TEMP TABLE.

В современном PostgreSQL обе формы дадут идентичный план. Различие — в эстетике.

Чек-лист

• Подзапрос в FROM, CTE и TEMP TABLE — три формы инкапсуляции с разной областью жизни.
• В PostgreSQL 12+ CTE inлайнятся по умолчанию, если используются один раз. Старый optimization fence ушёл.
• MATERIALIZED возвращает старое поведение — гарантирует одно вычисление.
• NOT MATERIALIZED форсирует inлайнинг даже при многократном использовании.
• TEMP TABLE — для случаев, когда нужен индекс, статистика или жизнь промежуточного результата дольше одного оператора.