Подзапрос, CTE или временная таблица — что выбрать?

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

Как рассуждать

Выстрой их по «весу» и области жизни.

• Подзапрос живёт внутри одного выражения. Хорош для короткой одноразовой вставки: посчитать среднее и сравнить с ним.
• CTE (WITH name AS (...)) — тот же подзапрос, но с именем и наверху запроса. Главный выигрыш — читаемость: сложный запрос раскладывается на именованные шаги, которые читаются сверху вниз. Плюс на CTE можно сослаться несколько раз и делать рекурсию (иерархии).
• Временная таблица — это уже физический объект: результат один раз посчитан и лёг на диск/в память сессии. Её можно проиндексировать и переиспользовать в нескольких последующих запросах.

Критерий выбора: нужна ли материализация и переиспользование между запросами (→ temp table), достаточно ли одного запроса и важна читаемость (→ CTE), или это мелкая разовая вставка (→ подзапрос).

Эталонный ответ

Начни с того, что для одного запроса подзапрос и CTE взаимозаменяемы по результату, разница — в читаемости и повторном использовании.

Подзапрос — компактно для разовой величины:

SELECT title, rental_rate
FROM film
WHERE rental_rate > (SELECT avg(rental_rate) FROM film);

CTE — когда шагов несколько и хочется имена. Здесь ещё и переиспользование результата:

WITH cat_rev AS (
  SELECT fc.category_id, sum(p.amount) AS revenue
  FROM payment p
  JOIN rental r    ON r.rental_id = p.rental_id
  JOIN inventory i ON i.inventory_id = r.inventory_id
  JOIN film_category fc ON fc.film_id = i.film_id
  GROUP BY fc.category_id
)
SELECT c.name, cr.revenue
FROM cat_rev cr
JOIN category c ON c.category_id = cr.category_id
WHERE cr.revenue > (SELECT avg(revenue) FROM cat_rev);

Тот же cat_rev использован дважды — с подзапросом пришлось бы дублировать всю выборку.

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

CREATE TEMP TABLE cat_rev AS
SELECT fc.category_id, sum(p.amount) AS revenue ...;
CREATE INDEX ON cat_rev(category_id);

Сильный ответ отметит нюанс: раньше CTE в Postgres были «оптимизационным барьером» (материализовались всегда), с версии 12 простые CTE могут встраиваться (inline), поэтому «CTE всегда медленнее подзапроса» — уже миф, зависит от версии и плана. И что CTE часто удобнее оборачивают оконные функции, по которым потом надо отфильтровать.

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

• Считают CTE «более быстрой версией подзапроса». Скорость тут ни при чём — выигрыш в читаемости и переиспользовании; план может быть идентичным.
• Плодят временные таблицы там, где хватает CTE, — лишняя материализация и мусор в сессии.
• Наоборот, гонят через один гигантский CTE-запрос то, что переиспользуется многими отчётами и выиграло бы от temp table с индексом.
• Забывают, что временная таблица живёт в пределах сессии/транзакции и не видна другим — и удивляются, что её «нет».