На ретро спринта отчётов команда собирается у доски: что за две недели тормозило, что переписывали дважды, где отчёт молчит вместо честной ошибки.

Дмитрий

Последний пункт — твой отчёт «сколько потратил каждый клиент». Цифры сходятся, всё зелёное. Открой и покажи всем.

Ты открываешь. Три SELECT, вложенных один в другой: внутренний считает сумму заказа, средний сворачивает по клиенту, внешний подставляет имя. Скобки закрываются где-то в самом низу.

Ботыр

Я его ревьюил. Точнее, пытался. Читается только изнутри наружу — к третьей скобке уже забываешь, что считал первый SELECT.

Т

Работает же. Что не так?

Дмитрий

Работает — не значит читается. Через месяц это будешь править ты, вслепую. Дай каждому шагу имя — и матрёшка развернётся в конвейер сверху вниз: суммы заказов, свёртка по клиенту, подстановка имени. Три шага, каждый видно.

Ботыр

А заодно давайте собьём все грабли спринта в один лист. Пять штук набралось — ровно по разу на юнит.

Дмитрий

Собери. Повесим над ревью.

Именованные шаги — это CTE, секция WITH; лист с граблями — чек-лист в конце юнита. Заодно разберём материализацию: считает ли Postgres CTE отдельно или встраивает в основной запрос. Начнём с CTE.

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

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

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

SELECT c.name, p.orders, p.spent
FROM (
    SELECT customer_id, count(*) AS orders, sum(cents)::bigint AS spent
    FROM (
        SELECT o.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
    ) AS order_totals
    GROUP BY customer_id
) AS p JOIN customers c ON c.id::text = p.customer_id
ORDER BY p.spent DESC;

Смысл тот же, но скобки вложены, имена шагов спрятаны в AS-псевдонимах в самом низу, а читать приходится от внутреннего SELECT к внешнему. CTE переворачивает это в линейный конвейер сверху вниз:

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;

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

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

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

За кофе Ботыр возвращается к теме:

Ботыр

Слушай, а я всегда выношу подзапрос в CTE — читал, что так быстрее.

Дмитрий

Читал где?

Ботыр

…в статье двенадцатого года.

Дмитрий

С тех пор PG12 переписал правило. «Быстрее» живёт в плане, не в тексте запроса, — а планы читаем в модуле 06. Ниже — с чего Postgres вообще решает считать CTE отдельно.

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

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

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

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

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

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

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

-- 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.)

Вывод:

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.

Типичные ошибки модуля 04

Это тот самый лист, который на ретро договорились повесить над ревью, — грабли, на которые модуль наступал по очереди: Карина, дважды выпавшая из отчёта (04-01 и 04-05), и «+40% выручки», которых не было (fan-out, 04-03). Все они общие: запрос не падает и не выдаёт ошибку — он молча отдаёт неверное число или не ту строку. Повесь её у себя над код-ревью.

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

• 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 и конкурентность»: что происходит, когда к этим данным одновременно тянутся несколько сессий.

На том же ретро, уже закрывая ноутбук, Ботыр кивает на лист с граблями:

Ботыр

А ведь всё это — про одну сессию. Что будет, когда к этим строкам полезут две кассы разом?

С этого вопроса модуль 05 и начнётся.