Common Table Expression (CTE) Materialization

In this lesson, you will learn how to control whether PostgreSQL computes a Common Table Expression (CTE) as a temporary result set or integrates it directly into the main query plan.

The Materialization Dilemma

By default, PostgreSQL (since version 12) treats CTEs as "inlinable." This means the optimizer treats the CTE like a subquery, potentially pushing filters down into it, merging it with the main query, and choosing the most efficient join path. While usually faster, this can sometimes lead to suboptimal plans if the CTE is computationally expensive and referenced multiple times.

"Materialization" forces the database to compute the CTE once, store the result in a temporary work table (in memory or on disk), and then reference that table whenever the CTE is called.

When to Force Materialization

You can control this behavior using the MATERIALIZED or NOT MATERIALIZED hint. Consider a report calculating a complex monthly summary:

WITH regional_stats AS MATERIALIZED (
    SELECT region_id, SUM(sales) as total_sales
    FROM transactions
    WHERE sale_date > '2023-01-01'
    GROUP BY region_id
)
SELECT r.name, rs.total_sales
FROM regions r
JOIN regional_stats rs ON r.id = rs.region_id
WHERE rs.total_sales > 10000;

In the example above, adding MATERIALIZED tells PostgreSQL: "Do not try to optimize this CTE by pushing filters inside; compute it fully once and use the static result."

Why do this?

1. Expensive Computations: If the CTE involves a heavy aggregate or a complex WINDOW function, you may not want the planner to execute that logic multiple times if it is referenced in a join and a subquery simultaneously.
2. Optimizer Guesswork: Occasionally, the cost model miscalculates the size of a CTE. Forcing materialization acts as a "fencing" mechanism, preventing the optimizer from making assumptions about the data distribution that lead to poor join choices.
3. Debugging: Materialized CTEs act as clear boundaries in an EXPLAIN plan, making it easier to see exactly how much time is spent preparing a specific data subset.

Use NOT MATERIALIZED if you want to keep the plan flexible for the optimizer, but use MATERIALIZED when you need to stabilize the execution cost of a repeated, complex data set.

Key Takeaway: Use the MATERIALIZED keyword to force PostgreSQL to compute a CTE as a discrete, reusable intermediate table, which prevents redundant calculations but limits the optimizer's ability to push predicates into the CTE.