Naming the steps of a query with WITH

From nested subqueries to named steps

In Practice: compose queries, “customers who spent more than the average customer” needed the per-customer total twice, written out in full both times. With a CTE you write it once, give it a name, and use the name:

WITH per_customer AS (
  SELECT c.id, c.name, sum(oi.quantity * oi.unit_price) AS spent
  FROM customers c
  JOIN orders o       ON o.customer_id = c.id
  JOIN order_items oi ON oi.order_id = o.id
  WHERE o.status <> 'cancelled'
  GROUP BY c.id, c.name
)
SELECT name, spent
FROM per_customer
WHERE spent > (SELECT avg(spent) FROM per_customer)
ORDER BY spent DESC;

    name    |  spent
------------+----------
 Ben Okafor | 41997.00
 Divya Iyer | 26695.00
 Farhan Ali | 24998.00

Same result as before; the query now reads top to bottom as “compute each customer’s spend, then keep those above the average”. A query can define several CTEs, separated by commas, and each can use the ones before it:

WITH order_totals AS (…),
     per_customer AS (… FROM order_totals …)
SELECT …;

Does naming a step make it slower?

A CTE looks like a separate step, so it is natural to wonder whether PostgreSQL computes it in full first. Usually it does not. A CTE that has no side effects and is referenced once is folded into the main query and optimized together with it.

EXPLAIN shows the plan PostgreSQL chooses. Here is a CTE of paid orders, then filtered to one customer:

EXPLAIN (COSTS OFF)
WITH paid AS (SELECT * FROM orders WHERE status = 'paid')
SELECT * FROM paid WHERE customer_id = 1;

 Seq Scan on orders
   Filter: ((status = 'paid'::text) AND (customer_id = 1))

There is no trace of the CTE. Both conditions are applied in one pass, as if you had written a single WHERE.

Add MATERIALIZED and the CTE is computed on its own first:

EXPLAIN (COSTS OFF)
WITH paid AS MATERIALIZED (SELECT * FROM orders WHERE status = 'paid')
SELECT * FROM paid WHERE customer_id = 1;

 CTE Scan on paid
   Filter: (customer_id = 1)
   CTE paid
     ->  Seq Scan on orders
           Filter: (status = 'paid'::text)

Now all paid orders are collected first, and only then filtered to customer 1. On a large table, that can mean building a big intermediate result the final query mostly throws away.

Check yourself

1. What problem does WITH solve in the “above-average spenders” query?

   Show answer

   The per-customer totals were needed twice. A CTE defines them once under a name that both parts of the query use, and the query reads as named steps.

   I could explain this without looking

2. Does PostgreSQL always compute a CTE in full before the main query?

   Show answer

   No. A side-effect-free, non-recursive CTE referenced once is folded into the main query by default. CTEs referenced several times are computed once, and recursive or data-modifying CTEs are always computed separately.

   I could explain this without looking

3. What did MATERIALIZED change in the paid-orders plan?

   Show answer

   PostgreSQL first collected every paid order (a separate CTE step) and only then filtered for customer 1, instead of applying both conditions in a single scan.

   I could explain this without looking