CTEs — when does PostgreSQL materialize vs inline? (Pre-12 vs 12+.)

Before PostgreSQL 12 a WITH CTE was always an optimization fence — materialized into a temp result the planner couldn't see through. Since PG12 the planner inlines eligible CTEs by default, and you control it explicitly with MATERIALIZED / NOT MATERIALIZED. This behavior change is a favorite interview question because it silently changes plans on upgrade.

Pre-12: CTEs were always materialized

WITH recent AS (
  SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT * FROM recent WHERE customer_id = 42;

On PG ≤ 11, recent was fully computed into a temporary result first, then filtered by customer_id = 42. The customer_id predicate could not be pushed down into the CTE, and any index on customer_id went unused. People exploited this as a deliberate optimization fence, but it often produced slow plans by accident.

PG12+: inlining by default

From PostgreSQL 12, the planner inlines a CTE — folding it into the outer query so predicates push down and indexes apply — when it is:

1. not recursive,
2. side-effect-free (no INSERT/UPDATE/DELETE/SELECT ... FOR UPDATE), and
3. referenced only once.

For the query above on PG12+, customer_id = 42 is pushed into the scan and an index can be used — typically much faster.

Forcing the behavior

WITH recent AS MATERIALIZED (        -- force a temp result (old fence)
  SELECT * FROM expensive_function_call()
)
SELECT ...;

WITH recent AS NOT MATERIALIZED (    -- force inlining
  SELECT * FROM orders WHERE ...
)
SELECT ...;

Recursive CTEs (WITH RECURSIVE) and multiply-referenced CTEs are always materialized regardless of version.