PostgreSQL Deep Dive: CTE Materialization — The Optimization Fence That Kills Your Query Performance

You wrote a query with a CTE. It should be fast — you have an index on the filter column. But the query takes 50 seconds. You look at EXPLAIN and see a full table scan where an index scan should be. The CTE is being materialized into a temporary table, and the outer query is scanning that temp table sequentially, completely ignoring every index on the original table.

This is the CTE materialization problem. Before PostgreSQL 12, it was the default. Since PG12, PostgreSQL tries to inline simple CTEs. But the planner doesn’t always make the right choice, and understanding when and why materialization happens — and how to override it — is one of the most impactful query optimization skills you can have.

How CTEs Work: Two Execution Strategies

A Common Table Expression (WITH clause) can be executed in two fundamentally different ways:

Inlined (Folded)

The CTE is merged into the parent query as if you’d written a subquery directly. The planner sees the full query structure and can push filters down, use indexes, reorder joins, and apply all of its usual optimizations.

-- This CTE
WITH active_users AS (
  SELECT id, email FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE email LIKE '%@gmail.com';

-- Gets rewritten internally to something like:
SELECT * FROM users
WHERE status = 'active' AND email LIKE '%@gmail.com';

The email LIKE '%@gmail.com' filter gets pushed down into the CTE. If there’s an index on (status, email), the planner can use it. The CTE is invisible at execution time — it’s just syntactic sugar.

Materialized

The CTE is executed once, its entire result set is written to a temporary table, and the outer query reads from that temporary table. The temporary table has no indexes. Every access is a sequential scan.

-- This CTE
WITH user_stats AS (
  SELECT user_id, COUNT(*) as order_count, SUM(amount) as total
  FROM orders
  GROUP BY user_id
)
SELECT * FROM user_stats WHERE order_count > 10;

-- Executes as:
-- Step 1: Scan orders, group by user_id, write results to temp table
-- Step 2: Scan temp table sequentially, filter order_count > 10

The materialization boundary is an optimization fence. The planner cannot push the WHERE order_count > 10 filter into the CTE. It cannot use any index on orders. It computes the full aggregation for every user, writes it all to a temp table, and then filters afterward.

The Default Behavior: PostgreSQL 12 and Later

Before PostgreSQL 12, CTEs were always materialized. This was the most common performance complaint about CTEs — they turned otherwise optimizable queries into two-phase operations with temp tables.

PostgreSQL 12 changed the default: CTEs are inlined (folded) by default when they are referenced once, are not data-modifying (INSERT/UPDATE/DELETE), and are side-effect-free. They are still materialized when referenced multiple times, or when the planner decides materialization is cheaper.

The decision heuristic:

Condition	PG11 and earlier	PG12+
Referenced once	Materialized	Inlined
Referenced multiple times	Materialized	Materialized
Data-modifying CTE	Materialized	Materialized
Recursive CTE	Materialized	Materialized

When Materialization Is the Right Choice

Materialization isn’t always bad. It’s the correct behavior when:

Multiple References

WITH user_totals AS (
  SELECT user_id, SUM(amount) as total
  FROM orders
  GROUP BY user_id
)
SELECT
  u.email,
  high.total as high_value_total,
  low.total as low_value_total
FROM users u
JOIN user_totals high ON high.user_id = u.id AND high.total > 1000
JOIN user_totals low ON low.user_id = u.id AND low.total < 100;

Here user_totals is referenced twice. If inlined, PostgreSQL would compute the aggregation twice — once for each join. Materializing it means computing once and reading twice. For expensive aggregations, materialization wins.

Expensive Function Calls

WITH enriched AS (
  SELECT
    id,
    very_expensive_function(data) as result
  FROM measurements
)
SELECT * FROM enriched e1
JOIN enriched e2 ON e1.result = e2.result AND e1.id < e2.id;

If inlined, very_expensive_function runs twice per row. Materializing it ensures the function runs once per row, and the self-join operates on the pre-computed results.

Side Effects or Volatility

If the CTE calls a volatile function (RANDOM(), NOW() at statement level, or a function with side effects), materialization ensures it runs once with consistent results. Inlining could cause the function to be evaluated multiple times with different results.

When Materialization Kills Performance

Single Reference with a Selective Filter

WITH active_orders AS (
  SELECT * FROM orders
  WHERE status = 'active'
)
SELECT * FROM active_orders
WHERE customer_id = 12345
  AND order_date > '2026-01-01';

In PG12+, this is inlined: the planner pushes customer_id = 12345 AND order_date > '2026-01-01' into the CTE, and if there’s an index on (customer_id, order_date), it uses it.

In PG11 and earlier (or if you force MATERIALIZED), the CTE scans every active order, writes them all to a temp table, and then scans the temp table for customer_id = 12345. You read 100,000 active orders to return 3.

Joining Against a Subquery

WITH recent_customers AS (
  SELECT DISTINCT customer_id
  FROM orders
  WHERE order_date > '2026-04-01'
)
SELECT o.*, c.name
FROM recent_customers rc
JOIN orders o ON o.customer_id = rc.customer_id
JOIN customers c ON c.id = rc.customer_id
WHERE o.status = 'pending';

If materialized, recent_customers is computed in full first. If inlined, the status = 'pending' filter can be applied during the CTE evaluation, potentially using an index on (customer_id, status, order_date). The planner also has the option to reorder the joins, which it can’t do across the materialization boundary.

Forcing the Behavior: MATERIALIZED and NOT MATERIALIZED

PostgreSQL 12+ lets you explicitly control materialization:

-- Force materialization (prevents double computation)
WITH user_stats AS MATERIALIZED (
  SELECT user_id, COUNT(*) as order_count
  FROM orders GROUP BY user_id
)
SELECT * FROM user_stats
JOIN user_stats us2 ON user_stats.user_id = us2.user_id;

-- Force inlining (allow index use on base tables)
WITH active_orders AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE status = 'active'
)
SELECT * FROM active_orders
WHERE customer_id = 12345;

MATERIALIZED forces a temp table. NOT MATERIALIZED forces inlining, even if the planner would normally materialize (e.g., for multiple references). Using NOT MATERIALIZED with multiple references risks computing the CTE multiple times, but if each reference only needs a small subset of the CTE’s output, it can still be faster.

Detecting Materialization in EXPLAIN

EXPLAIN (ANALYZE, COSTS)
WITH active_orders AS (
  SELECT * FROM orders WHERE status = 'active'
)
SELECT * FROM active_orders WHERE customer_id = 12345;

Look for these in the EXPLAIN output:

• Inlined (folded): The CTE name doesn’t appear in the plan. You see a direct scan of the underlying table with all predicates combined. This is what you want for single-reference CTEs with selective filters.

• Materialized: You see a CTE Scan node reading from a Subquery Scan or Aggregate node. The CTE Scan is always a sequential scan with no index. If the CTE produces many rows but the outer query only needs a few, materialization is wasteful.

CTE Scan on active_orders  (cost=0.00..15432.00 rows=100000 width=64)
  Filter: (customer_id = 12345)

Compare with the inlined version:

Index Scan using idx_orders_customer_status on orders  (cost=0.42..8.44 rows=3 width=64)
  Index Cond: (customer_id = 12345)
  Filter: (status = 'active')

The difference is orders of magnitude.

Data-Modifying CTEs: Always Materialized

CTEs that contain INSERT, UPDATE, DELETE, or MERGE are always materialized, regardless of the PG version. This is necessary because the data modification has side effects that must happen exactly once.

WITH archived AS (
  DELETE FROM orders
  WHERE order_date < '2025-01-01'
  RETURNING *
)
INSERT INTO order_archive SELECT * FROM archived;

The DELETE runs first, its results go into a temp table, and the INSERT reads from that temp table. This is correct and cannot be inlined.

However, the outer query’s access to the CTE is still a sequential scan of the temp table. For data-modifying CTEs with a small result set, this is fine. For large result sets, consider batching the operation instead of wrapping it in a single CTE.

Recursive CTEs: Always Materialized

Recursive CTEs (WITH RECURSIVE) are always materialized for the working table during each iteration. This is inherent to how recursive evaluation works — each iteration produces new rows that are added to the result and checked for the termination condition.

You cannot use NOT MATERIALIZED on recursive CTEs. The materialization is not optional.

The Gotcha: Side-Effect Functions Inside Inlined CTEs

When PostgreSQL inlines a CTE, the CTE’s expression can be evaluated multiple times if the optimizer decides to restructure the query. If the CTE calls a volatile function, this means the function gets called more times than you expect:

-- If inlined, this might evaluate generate_report() multiple times
WITH report_data AS (
  SELECT id, generate_report(id) as report
  FROM accounts
)
SELECT * FROM report_data WHERE report IS NOT NULL;

If generate_report() is expensive or has side effects, inlining is dangerous. Force materialization:

WITH report_data AS MATERIALIZED (
  SELECT id, generate_report(id) as report
  FROM accounts
)
SELECT * FROM report_data WHERE report IS NOT NULL;

This is the exception that proves the rule: materialization is usually a performance problem, but it’s the correct behavior when your CTE has side effects or volatile functions.

Practical SQL: Comparing CTE vs Subquery Performance

-- Version 1: CTE (might be materialized)
EXPLAIN (ANALYZE)
WITH recent AS (
  SELECT * FROM orders WHERE order_date > '2026-04-01'
)
SELECT customer_id, SUM(amount) as total
FROM recent
WHERE status = 'completed'
GROUP BY customer_id;

-- Version 2: Subquery (always inline)
EXPLAIN (ANALYZE)
SELECT customer_id, SUM(amount) as total
FROM (
  SELECT * FROM orders WHERE order_date > '2026-04-01'
) recent
WHERE status = 'completed'
GROUP BY customer_id;

-- Version 3: NOT MATERIALIZED CTE (force inline)
EXPLAIN (ANALYZE)
WITH recent AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE order_date > '2026-04-01'
)
SELECT customer_id, SUM(amount) as total
FROM recent
WHERE status = 'completed'
GROUP BY customer_id;

Compare the plans. If Version 1 shows a CTE Scan and Versions 2-3 show an Index Scan, the CTE was materialized and you need NOT MATERIALIZED or a subquery rewrite.

Key Takeaways

• PostgreSQL 12+ inlines CTEs by default when they’re referenced once and are not data-modifying. Before PG12, CTEs were always materialized.
• Materialization creates an optimization fence: the planner cannot push filters from the outer query into the CTE, cannot use indexes on the base tables, and cannot reorder joins across the CTE boundary.
• A CTE Scan in EXPLAIN is always a sequential scan with no indexes. If the outer query only needs a small subset of the CTE’s output, this is wasteful.
• Use NOT MATERIALIZED to force inlining when the planner incorrectly materializes a single-reference CTE. Use MATERIALIZED to prevent duplicate computation when a CTE is referenced multiple times or contains expensive/volatile functions.
• Data-modifying CTEs (INSERT/UPDATE/DELETE/MERGE) and recursive CTEs are always materialized — this is not configurable.
• If you’re on PG11 or earlier, consider rewriting performance-critical CTEs as subqueries to avoid forced materialization.
• The safest approach for new code on PG12+: write CTEs normally, check EXPLAIN for CTE Scan nodes, and add NOT MATERIALIZED if you see unexpected sequential scans.

What’s Next

Tomorrow we’ll look at DDL locking — the subtle and surprising locks that ALTER TABLE, CREATE INDEX, and other DDL commands acquire, and why seemingly safe schema changes can block your entire application.

---

Previous: Synchronous Commit and Durability Trade-offs