Postgres CTEs: When WITH Helps and When the Optimizer Wishes You Had Not Used It

CTEs are how I keep complex queries readable. They are also, until Postgres 12, a notorious optimization barrier. The version-12 change relaxed that, but the relaxation has its own subtleties most teams have not absorbed.

Here is what I tell people when they ask whether to write a query as a CTE or a subquery.

The pre-12 behavior

Before Postgres 12, CTEs were always materialized. The planner ran the CTE, dumped the result into a temporary structure, then ran the outer query against that structure. No predicate pushdown, no inlining, no shape-shifting.

This was sometimes useful (forcing a particular execution shape) and often disastrous (turning a query that could have been a 10ms index scan into a 5-second materialize-and-filter). Many older guides treat CTEs as universally an optimization fence. That advice is now outdated.

The 12+ behavior

In Postgres 12+, simple CTEs are inlined by default. The planner treats WITH foo AS (...) SELECT ... FROM foo mostly the same as it would treat SELECT ... FROM (...) AS foo. Predicates push down. The execution shape can change. The performance is usually equivalent to a subquery.

There are exceptions: CTEs that are recursive, that have side effects (DML), or that are referenced more than once retain materialization to preserve semantics. But for the most common case — read-only, single-reference — Postgres now handles CTEs the way developers expect.

When to force materialization

Sometimes you actually want the materialization barrier. The CTE produces a result that is expensive to compute and that you reference multiple times in the outer query, or that you want the planner to treat as a fixed input.

WITH expensive_calc AS MATERIALIZED (
  SELECT customer_id, sum(total_cents) AS lifetime_cents
  FROM orders
  GROUP BY customer_id
)
SELECT *
FROM customers c
JOIN expensive_calc e ON e.customer_id = c.id
WHERE c.country = 'US'
  AND e.lifetime_cents > 10000;

The MATERIALIZED keyword tells Postgres: "compute this CTE once, hold the result, then use it." Without it, the planner might inline the aggregation into the outer query and compute it differently for the country = 'US' subset, which is sometimes worse.

This is one of the rare cases where the developer knows something the planner does not — namely, that you have already thought through the cost of the calculation and want it done once.

When to force inlining

The opposite case: you have a CTE that the planner is materializing because of one of the implicit rules (referenced multiple times, mostly), but you actually want it inlined for predicate pushdown. Use NOT MATERIALIZED:

WITH us_customers AS NOT MATERIALIZED (
  SELECT * FROM customers WHERE country = 'US'
)
SELECT * FROM us_customers WHERE created_at >= now() - interval '30 days';

Now the planner can push the created_at filter down into the underlying customers scan, instead of materializing all US customers and then filtering.

In practice, I rarely need NOT MATERIALIZED. The default behavior in 12+ is what I want. The keyword exists for the cases where it is not.

Recursive CTEs are their own thing

Recursive CTEs (WITH RECURSIVE) are always materialized; the recursion semantics require it. They are also genuinely powerful for hierarchical queries:

WITH RECURSIVE org_tree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM org_units
  WHERE parent_id IS NULL
  UNION ALL
  SELECT o.id, o.parent_id, o.name, t.depth + 1
  FROM org_units o
  JOIN org_tree t ON t.id = o.parent_id
)
SELECT * FROM org_tree ORDER BY depth, name;

The optimization barrier is fine here — the recursive structure is the work. What I do watch for: recursive CTEs that go deeper than expected, sometimes infinite. Always have a depth termination clause.

A common anti-pattern

Using a CTE to pretend to factor logic out of a query, when the CTE is referenced only once and inlined.

-- Pre-12: this was a real optimization barrier
-- Post-12: this is identical to writing it inline
WITH big_orders AS (
  SELECT * FROM orders WHERE total_cents > 10000
)
SELECT * FROM big_orders WHERE customer_id = 123;

In Postgres 12+, this is exactly equivalent to:

SELECT * FROM orders
WHERE total_cents > 10000
  AND customer_id = 123;

If the CTE is just for readability and you are on 12+, that is fine — the planner produces the same plan. Just know that on older Postgres, the CTE forces a different plan that may be much slower.

How I decide

For a new query on a modern Postgres:

• If the CTE makes the query more readable and is referenced once, use it freely.
• If the CTE expresses a real computation that is referenced multiple times, use MATERIALIZED to be explicit.
• If the CTE is recursive, that is the only way to write the query anyway.
• If you are on Postgres 11 or older, treat every CTE as an optimization barrier and rewrite as a subquery if performance matters.

The behavior change in 12 was significant. If you are reading old advice that says "never use CTEs in performance-critical queries," check the Postgres version it was written for.