At the reports-sprint retro the team gathers at the whiteboard: what dragged over the two weeks, what got rewritten twice, where a report stayed silent instead of erroring.

Dmitry

Last item — your report, "how much each customer spent." The numbers add up, all green. Open it and show everyone.

You open it. Three SELECTs nested one inside another: the inner one sums an order, the middle collapses per customer, the outer substitutes the name. The parentheses close somewhere at the very bottom.

Botyr

I reviewed it. Tried to, rather. You can only read it inside out — by the third bracket you've already forgotten what the first SELECT computed.

Y

It works, though. What's wrong with it?

Dmitry

Working isn't the same as readable. In a month you'll fix this blind. Give each step a name — and the matryoshka unfolds into a top-down pipeline: order totals, roll-up per customer, name substitution. Three steps, each visible.

Botyr

And while we're at it, let's knock all the sprint's rakes into one sheet. Five of them already — exactly one per unit.

Dmitry

Do it. We'll pin it above review.

Named steps are a CTE, the WITH clause; the sheet of rakes is the checklist at the end of this unit. Along the way we'll unpack materialization: whether Postgres computes a CTE separately or inlines it into the main query. We'll start with the CTE.

CTE: named building blocks

WITH name AS (query) declares a temporary result that you then reference by name, like a table. CTEs can be chained: the second references the first, the main query references both.

First look at how the same three steps read without WITH — a subquery in the FROM of another subquery. You can only read it from the inside out, holding in your head what each nested level computes:

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;

Same meaning, but the parentheses nest, the step names hide in AS aliases at the very bottom, and you read from the inner SELECT outward. A CTE flips this into a linear top-down pipeline:

WITH order_totals AS (        -- step 1: each order's total from line items
    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 (             -- step 2: collapse orders per customer
    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          -- step 3: substitute the name
FROM per_customer p JOIN customers c ON c.id::text = p.customer_id
ORDER BY p.spent DESC;

Each step is a separate named block, and the result flows top to bottom into the next:

WITH order_totals AS (...)    step 1 · each order's total from line items
         │
         ▼
     per_customer AS (...)    step 2 · collapse orders per customer
         │
         ▼
   SELECT … JOIN customers    step 3 · substitute the customer name

The main value of a CTE for an application is readability and reuse of an intermediate result — not "speedup." A CTE on its own doesn't make a query faster.

Over coffee Botyr circles back to the topic:

Botyr

Listen, I always pull a subquery into a CTE — read that it's faster that way.

Dmitry

Read it where?

Botyr

…in an article from '12.

Dmitry

PG12 rewrote the rule since then. "Faster" lives in the plan, not the query text — and plans we read in module 06. Below is what makes Postgres compute a CTE separately at all.

Materialization: a fence vs inlining

Here's the subtlety. Postgres can treat a CTE in two ways:

• Inline — substitute the CTE's body into the main query, like an ordinary subquery. Then the planner sees the whole query and can, for example, push a filter into the CTE.
• Materialize (fence) — compute the CTE separately, once, store the result in a temporary buffer, and read from it afterwards. The optimizer doesn't peek behind that "fence."

Since PG12 the default rule is: if a CTE is referenced once — it's inlined; if more than once (or it contains a write/VOLATILE function) — it's materialized (logically: computing once and reusing is cheaper than twice). A VOLATILE function is one whose result may change from call to call on the same arguments (random() or clock_timestamp(), say); inlining copies of it and calling it several times would be unsafe, so Postgres puts up a fence (volatility in detail — module 09). These defaults can be overridden with keywords: AS MATERIALIZED forces the fence, AS NOT MATERIALIZED forces inlining.

Our second query references order_totals twice — in FROM and in a scalar subquery for the grand total — so it's materialized (we wrote AS MATERIALIZED explicitly, but even without the keyword the default would be the same).

What our code shows

Two queries in query.sql. The first is a two-CTE pipeline (CustomerSpend, above). The second is a CTE used twice:

-- 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 is read in FROM and in (SELECT sum(cents) FROM order_totals) — hence each order's share of the grand total.

Running it

Bring up the sandbox (from the repo root) and apply the Brew base schema:

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 is the default. From inside the unit directory it's make db-reset, make run.)

Output:

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 материализует его (считает один раз, переиспользует).

(The demo prints in Russian.) The first query read as three steps and collapsed spend per customer (totals come from order_items, so Alice = 9.70 + 9.60 = 19.30). The second reused one CTE twice and computed each order's share of the grand total (9.70 + 3.00 + 9.60 = 22.30 → 43.5% + 13.5% + 43.0%).

The fence

What we simplified.

• The difference between inlining and materialization is visible not in the result (it's the same) but in the plan: a materialized CTE puts up a "fence" the optimizer won't push filters through, and on large data a stray AS MATERIALIZED sometimes hurts (the server computes the whole CTE even though only a couple of rows are needed outside). You can see this only via EXPLAIN — that's module 06, so here we only name the levers without measuring them.
• A CTE is about readability, not speed. The belief "I'll rewrite the subquery into a WITH and it'll be faster" is a myth (before PG12 materialization was always on and sometimes even hurt).
• A recursive CTE (WITH RECURSIVE) is a separate big topic: traversing trees and graphs; unit 08-04 is devoted to it.

Common mistakes in module 04

This is the very sheet the retro agreed to pin above review — the rakes the module stepped on one by one: Karina, dropped from the report twice (04-01 and 04-05), and the "+40% revenue" that was never there (fan-out, 04-03). They all share a trait: the query doesn't fail or raise an error — it silently returns a wrong number or the wrong row. Pin it above your code review.

Takeaways

• A CTE (WITH name AS (...)) gives an intermediate result a name; the query turns from a "subquery matryoshka" into a linear pipeline of steps.
• CTEs can be chained: the next references the previous — that's the main value for readability.
• A CTE on its own doesn't speed up a query; it's about code structure, not performance.
• Since PG12: one reference → the CTE is inlined; more than one (or a write inside) → it's materialized. The levers are AS MATERIALIZED / AS NOT MATERIALIZED.
• The inline/materialize difference shows up in the plan (EXPLAIN, module 06), not in the result.

That completes module 04, "Querying across tables": you can connect tables (JOIN/self-join), collapse rows into summaries (GROUP BY/HAVING, DISTINCT ON), ask questions with questions (subqueries, EXISTS vs IN), and assemble readable pipelines (CTE). Next up — module 05 "Transactions, MVCC, and concurrency": what happens when several sessions reach for this data at once.

At that same retro, already closing his laptop, Botyr nods at the sheet of rakes:

Botyr

And all of this — it's about one session. What happens when two registers reach for these rows at once?

That's where module 05 begins.