CTE и материализация

Отчёты в Brew редко умещаются в один плоский SELECT. «Сколько потратил каждый клиент» — это сначала посчитать сумму каждого заказа из позиций, потом свернуть заказы по клиенту, потом подставить имя. Запихнуть всё это во вложенные подзапросы можно, но читать такой запрос — как разгадывать матрёшку изнутри наружу.

CTE (Common Table Expression, секция WITH) переворачивает это: даёт каждому промежуточному шагу имя, и запрос читается сверху вниз, шаг за шагом. А заодно мы разберём материализацию — вычисляет ли Postgres CTE отдельно (и кэширует результат) или встраивает его в основной запрос.

CTE: именованные шаги-кирпичи

WITH имя AS (запрос) объявляет временный результат, на который дальше ссылаются по имени, как на таблицу. CTE можно цепочкой: второй ссылается на первый, основной запрос — на оба. Это превращает «подзапрос в подзапросе» в линейный конвейер:

sql

WITH order_totals AS (        -- шаг 1: сумма каждого заказа из позиций
    SELECT o.id AS order_id, o.customer_id,
           sum(oi.quantity * oi.unit_price)::bigint AS cents
    FROM orders o JOIN order_items oi ON oi.order_id = o.id
    GROUP BY o.id, o.customer_id
),
per_customer AS (             -- шаг 2: свернуть заказы по клиенту
    SELECT customer_id, count(*) AS orders, sum(cents)::bigint AS spent
    FROM order_totals GROUP BY customer_id
)
SELECT c.name, p.orders, p.spent          -- шаг 3: подставить имя
FROM per_customer p JOIN customers c ON c.id::text = p.customer_id
ORDER BY p.spent DESC;

Каждый шаг — отдельный именованный кирпич, и результат течёт сверху вниз в следующий:

plaintext

WITH order_totals AS (...)    шаг 1 · сумма каждого заказа из позиций
         │
         ▼
     per_customer AS (...)    шаг 2 · свернуть заказы по клиенту
         │
         ▼
   SELECT … JOIN customers    шаг 3 · подставить имя клиента

Главная ценность CTE для приложения — читаемость и переиспользование промежуточного результата, а не «ускорение». CTE сам по себе запрос не ускоряет.

Материализация: фенс против встраивания

Тут начинается тонкость. Postgres может обойтись с CTE двумя способами:

Встроить (inline) — подставить тело CTE в основной запрос, как обычный подзапрос. Тогда планировщик видит запрос целиком и может, например, протолкнуть фильтр внутрь CTE.
Материализовать (фенс) — вычислить CTE отдельно, один раз, сложить результат во временный буфер и дальше читать из него. Оптимизатор за этот «забор» не заглядывает.

Начиная с PG12 правило по умолчанию такое: если на CTE ссылаются один раз — он встраивается; если больше одного раза (или внутри есть запись/VOLATILE-функция) — материализуется (логично: считать один раз и переиспользовать дешевле, чем дважды). Эти умолчания можно переопределить ключевыми словами: AS MATERIALIZED форсирует забор, AS NOT MATERIALIZED форсирует встраивание.

	встраивание (inline)	материализация (фенс)
что делает	тело `CTE` подставляется в основной запрос	`CTE` считается отдельно, один раз, в буфер
оптимизатор	видит запрос целиком, проталкивает фильтры внутрь	за «забор» не заглядывает
умолчание (PG12+)	на `CTE` ссылаются один раз	ссылок больше одной (или запись/`VOLATILE`)
форс	`AS NOT MATERIALIZED`	`AS MATERIALIZED`

Наш второй запрос ссылается на order_totals дважды — в FROM и в scalar-подзапросе с общим итогом, — поэтому он материализуется (мы написали AS MATERIALIZED явно, но и без ключевого слова умолчание было бы тем же).

Что показывает наш код

Два запроса в query.sql. Первый — конвейер из двух CTE (CustomerSpend, см. выше). Второй — CTE, использованный дважды:

sql

-- name: OrderShareOfTotal :many
WITH order_totals AS MATERIALIZED (
    SELECT o.id AS order_id, sum(oi.quantity * oi.unit_price)::bigint AS cents
    FROM orders o JOIN order_items oi ON oi.order_id = o.id GROUP BY o.id
)
SELECT order_id, cents,
       round(100.0 * cents / (SELECT sum(cents) FROM order_totals), 1)::text AS pct
FROM order_totals ORDER BY order_id;

order_totals читается и в FROM, и в (SELECT sum(cents) FROM order_totals) — отсюда доля каждого заказа в общем итоге.

Запуск

Подними песочницу (из корня репозитория) и накати схему Brew:

docker compose up -d
make lecture L=04-querying-across-tables/04-06-ctes-and-materialization T=db-reset
make lecture L=04-querying-across-tables/04-06-ctes-and-materialization

(T=run — значение по умолчанию. Изнутри каталога юнита это make db-reset, make run.)

Вывод:

plaintext

1) Траты клиента — CTE-конвейер (order_totals → per_customer → имя):
   клиент           заказов потрачено
   Алиса Иванова          2     19.30
   Борис Петров           1      3.00
   → суммы посчитаны из позиций order_items; Карины нет — у неё заказов нет.
 
2) Доля заказа от общего — CTE order_totals использован дважды (FROM + scalar-подзапрос):
   заказ      сумма  доля,%
   #1          9.70    43.5
   #2          3.00    13.5
   #3          9.60    43.0
   → ссылок на CTE две → Postgres материализует его (считает один раз, переиспользует).

Первый запрос прочитался как три шага и свернул траты по клиенту (суммы — из позиций order_items, поэтому Алиса = 9.70 + 9.60 = 19.30). Второй переиспользовал один CTE дважды и посчитал долю каждого заказа в общем итоге (9.70 + 3.00 + 9.60 = 22.30 → 43.5% + 13.5% + 43.0%).

Заборчик

Что мы упростили.

Разница между встраиванием и материализацией видна не в результате (он одинаков), а в плане: материализованный CTE ставит «забор», за который оптимизатор не проталкивает фильтры, и на больших данных лишний AS MATERIALIZED иногда мешает (сервер считает весь CTE, хотя снаружи нужна пара строк). Увидеть это можно только через EXPLAIN — это модуль 06, поэтому здесь мы лишь называем рычаги, не меряя их.
CTE — про читаемость, а не про скорость. Вера «перепишу подзапрос в WITH — станет быстрее» — миф (до PG12 материализация была всегда и иногда даже вредила).
Рекурсивный CTE (WITH RECURSIVE) — отдельная большая тема: обход деревьев и графов; ему посвящён юнит 08-04.

Что забрать с собой

CTE (WITH имя AS (...)) даёт промежуточному результату имя; запрос из «матрёшки подзапросов» превращается в линейный конвейер шагов.
CTE можно цепочкой: следующий ссылается на предыдущий — это и есть главная ценность для читаемости.
CTE сам по себе не ускоряет запрос; он про структуру кода, а не про производительность.
С PG12: ссылка одна → CTE встраивается; ссылок больше одной (или есть запись) → материализуется. Рычаги — AS MATERIALIZED / AS NOT MATERIALIZED.
Разница встраивание/материализация проявляется в плане (EXPLAIN, модуль 06), а не в результате.

На этом модуль 04 «Запросы по таблицам» завершён: ты умеешь связывать таблицы (JOIN/self-join), сворачивать строки в сводки (GROUP BY/HAVING, DISTINCT ON), задавать вопросы вопросами (подзапросы, EXISTS vs IN) и собирать читаемые конвейеры (CTE). Дальше — модуль 05 «Транзакции, MVCC и конкурентность»: что происходит, когда к этим данным одновременно тянутся несколько сессий.