What is a materialized CTE and how does it differ from a regular CTE?

A materialized CTE is computed once and stored in temporary memory (like a temp table). A regular (non-materialized) CTE may be inlined by the optimizer, meaning its logic is merged into the main query and potentially executed multiple times if referenced multiple times.

Key differences:

1. Execution: Materialized = once, Non-materialized = potentially multiple times

2. Storage: Materialized = temp memory, Non-materialized = no storage (inlined)

3. Performance: Materialized = better for reuse, Non-materialized = better for single use

4. Syntax: PostgreSQL uses MATERIALIZED hint, SQL Server auto-materializes

Example:

WITH stats AS MATERIALIZED (SELECT department, AVG(salary) FROM employees GROUP BY department)

SELECT * FROM stats WHERE avg_salary > 70000

UNION ALL

SELECT * FROM stats WHERE avg_salary <= 70000;

With MATERIALIZED: Aggregation runs once, results reused

Without MATERIALIZED: Aggregation might run twice (once per SELECT)

PostgreSQL 12+ defaults to NOT MATERIALIZED (inlining). Must explicitly add MATERIALIZED hint for multi-use CTEs.

When should you materialize a CTE? When should you NOT materialize?

Materialize when:

1. CTE referenced 2+ times in query (most common reason)

2. CTE computation is expensive (aggregations, joins, window functions)

3. Execution plan shows CTE executing multiple times

4. Large table scans that would be repeated

Example: Dashboard with 3 charts using same monthly aggregation

Don't materialize when:

1. CTE used only once (materialization adds overhead)

2. CTE is simple (SELECT * FROM table WHERE id = 1)

3. Small result sets (overhead > benefit)

4. Optimizer can push down filters (inlining allows better optimization)

Example: Single-use CTE with filter that can be pushed down

Rule of thumb: Break-even point is usually 2 references. 1 reference = overhead, 2+ references = benefit.

Always test with EXPLAIN ANALYZE to verify. Sometimes optimizer surprises you.

How does PostgreSQL 12+ handle CTE materialization differently than earlier versions?

PostgreSQL 12 changed default CTE behavior:

PostgreSQL 11 and earlier:

- Default: MATERIALIZED (always computed once and stored)

- CTEs acted like optimization fences (prevented optimizer from pushing filters)

- Good for preventing duplicate execution, bad for optimization

PostgreSQL 12+:

- Default: NOT MATERIALIZED (inlined into main query)

- Optimizer can push filters into CTE for better performance

- Bad for multi-use CTEs (can execute multiple times)