CTE materialization and query performance

Consider:

At first glance, you might imagine PostgreSQL doing this:

That would mean processing all 10,000 customers and then keeping one.

Current PostgreSQL does not necessarily do that.

For a non-recursive, side-effect-free SELECT CTE that is referenced once, PostgreSQL can fold the CTE into the parent query.

Conceptually, it can optimize the query more like:

The CTE remains useful for organizing the SQL, but it does not necessarily create a separate intermediate result during execution.

Run:

You should see a plan like this:

Your timing and buffer values will probably differ from the ones shown here.

There is no CTE Scan in this plan at all. The CTE was folded away, and PostgreSQL pushed:

down to the scan of customers. Because customers.id is a primary key and already has an index, that condition became an Index Cond and the query read a single row.

The exact execution plan can vary, but the important idea is that PostgreSQL is allowed to optimize the CTE together with the outer query rather than first producing all CTE rows.

When PostgreSQL materializes a CTE, it calculates the CTE separately and makes that result available to the rest of the query.

Conceptually:

The outer query then works with the CTE result rather than optimizing directly against the underlying table.

This can be useful in some situations and harmful in others.

PostgreSQL allows us to explicitly request materialization:

Now PostgreSQL is instructed to calculate selected_customers separately.

Run:

This time the plan looks quite different:

Three lines are worth reading closely:

• Seq Scan on customers ... rows=10000 shows that the CTE read all 10,000 customers.

• Rows Removed by Filter: 9999 shows that id = 9189 was applied afterwards, to the CTE result.

• Storage: Memory Maximum Storage: 533kB shows that the intermediate result was actually stored.

The index on customers.id was never used, and the execution time went from a fraction of a millisecond to several milliseconds.

The important difference is conceptual:

versus:

Materialization can therefore prevent an outer condition from being pushed directly into the underlying table scan.

PostgreSQL also provides:

For example:

The plan returns to the folded shape:

NOT MATERIALIZED tells PostgreSQL to allow the CTE to be merged into the parent query rather than forcing a separately calculated result.

This can make it possible for conditions such as:

to participate directly in optimization of the underlying query.

A useful mental model is:

For a non-recursive, side-effect-free `SELECT` CTE, PostgreSQL can fold the CTE into its parent query.

By default, a useful rule to remember is:

This is not a reason to automatically add NOT MATERIALIZED whenever a CTE is referenced more than once.

There is a trade-off.

Imagine:

customer_data is referenced twice.

You should see a plan like this:

There is one Seq Scan on customers but two CTE Scan nodes. PostgreSQL calculated the CTE once and both sides of the join read that stored result.

If PostgreSQL independently expanded the CTE into both locations, some work might be repeated.

Materializing the CTE allows PostgreSQL to calculate it once and reuse its output.

Conceptually:

This is one reason materialization can be useful.

Now imagine a CTE contains many rows:

but the outer query needs only:

If the CTE is materialized first, PostgreSQL may have to calculate the larger intermediate result before applying the outer filter.

If the CTE can be folded into the outer query, PostgreSQL may instead apply the filter while accessing the base table.

Conceptually:

versus:

That is exactly the difference the two plans earlier in this chapter showed.

So materialization can sometimes prevent useful query optimizations.

Materialization can also be beneficial.

Imagine a CTE performs an expensive calculation and the result is used several times:

If the result is materialized, PostgreSQL can perform the expensive work once and reuse the result.

If the CTE were instead expanded into each use, the calculation could potentially be repeated.

So:

while:

There is no rule that one is always faster.

Seeing:

does not mean:

Similarly:

does not mean:

They change how PostgreSQL is allowed to plan the CTE.

Whether that helps depends on the query.

If performance matters, compare the execution plans with:

rather than guessing.

The folding behavior discussed above applies to non-recursive, side-effect-free SELECT CTEs.

A recursive CTE is different.

As you saw in the previous chapter, PostgreSQL evaluates recursive CTEs iteratively using a working table.

They are not treated like a simple non-recursive CTE that can be folded into the parent query.

Data-modifying CTEs are also different because they perform operations such as:

The materialization discussion in this chapter is primarily about ordinary SELECT CTEs.

You may encounter older PostgreSQL advice saying:

That description does not match current PostgreSQL behavior.

For current PostgreSQL, a suitable non-recursive SELECT CTE can be folded into the parent query and optimized together with it.

This means the first question when deciding whether to use a CTE should often be:

Then, if the query is performance-sensitive:

and decide whether materialization behavior matters.

Think of the two possibilities like this:

versus:

MATERIALIZED can be useful when calculating a result once and reusing it is valuable.

NOT MATERIALIZED can be useful when allowing the outer query to optimize directly against the underlying tables is more valuable.

The important point is:

A CTE is a way to structure SQL, but its execution is still a query-planning decision. Use `EXPLAIN ANALYZE` when the performance difference matters.