Chaining Multiple CTEs: Readability vs. Performance Reality

1. Basic principle: each CTE builds on the previous one

A WITH clause can contain several comma separated CTE definitions, where each subsequent CTE may reference every CTE defined before it. That creates a chain of named intermediate steps, each of which stays simple and traceable on its own, while the overall complexity of the analysis only emerges from the interplay of all the steps together. That differs fundamentally from a deeply nested subquery, where the reader has to think from the inside out or from the outside in to follow the logic.

A typical pattern is a chain of three to five steps: first a CTE that filters and preprocesses raw data, then a CTE that aggregates on top of that, followed by a CTE that computes metrics via window functions, and finally the actual main query that shapes the final result from the last CTE. Every single step can be tested in isolation by temporarily replacing the WITH chain with a plain SELECT against the respective intermediate CTE.

WITH filtered_orders AS (
    SELECT customer_id, order_id, total_amount, order_date
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '365 days'
      AND status = 'completed'
),
customer_totals AS (
    SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent
    FROM filtered_orders
    GROUP BY customer_id
),
ranked_customers AS (
    SELECT
        customer_id,
        order_count,
        total_spent,
        PERCENT_RANK() OVER (ORDER BY total_spent) AS spend_percentile
    FROM customer_totals
)
SELECT customer_id, order_count, total_spent, spend_percentile
FROM ranked_customers
WHERE spend_percentile >= 0.9
ORDER BY total_spent DESC;

2. Readability through descriptive intermediate names instead of anonymous subqueries

The biggest practical advantage of chained CTEs lies in the naming: every intermediate step gets its own, business meaningful name that communicates the role of that step within the overall logic before the actual code is even read. An anonymous, nested subquery offers no such option, its purpose has to be inferred from context and position within the outer query, which becomes increasingly tedious with several levels of nesting.

This naming pays off especially during code reviews and later maintenance by other team members. A well named CTE chain reads almost like a documented sequence of steps, while a deeply nested subquery construct often takes considerable rethinking to understand again after a few months, even for the original author. The documentation value of a clear CTE chain should not be underestimated when deciding on this style.

3. Materialization behavior before PostgreSQL 12

Up through PostgreSQL 11, every CTE was fundamentally treated as an independent, materialized intermediate result, regardless of how it was used in the outer query. That meant the optimizer treated a CTE somewhat like a temporary table: filter conditions from the outer query could not be pushed into the CTE, and the optimizer could not jointly optimize the CTE definition together with the rest of the query.

This behavior had a practical upside that was deliberately exploited: a CTE as a so called optimization fence, that is a deliberate optimization boundary, could be used specifically to force the optimizer into a certain execution order, when an automatic rewrite by the optimizer had, from experience, led to a worse plan. The downside was that the same materialization was also forced when it was not actually needed from a business logic standpoint, which wasted memory and time unnecessarily on large intermediate results.

4. The shift since PostgreSQL 12: inlining as the default behavior

Since PostgreSQL 12, the default behavior has fundamentally changed: a CTE is now, where possible, automatically folded into the surrounding query, similar to a subquery, instead of being forced into materialization. That allows the optimizer to push filter conditions from the outer query into the CTE, use indexes across CTE boundaries, and overall build a global execution plan for the entire chain, which in many cases leads to noticeably better performance than the previous, forced materialized behavior.

If the old, materialized semantics are still deliberately needed for a specific use case, for example a recursive CTE, a CTE with side effects through data modifying statements, or deliberately as an optimization fence, this can be forced explicitly using the MATERIALIZED keyword on the CTE definition. Conversely, NOT MATERIALIZED explicitly requests inlining even where the optimizer might otherwise decide differently.

-- PostgreSQL 12+: explicit control over materialization
WITH filtered_orders AS MATERIALIZED (
    SELECT customer_id, order_id, total_amount
    FROM orders
    WHERE status = 'completed'
),
recent_orders AS NOT MATERIALIZED (
    SELECT * FROM filtered_orders
    WHERE order_id > 1000000
)
SELECT customer_id, COUNT(*) FROM recent_orders GROUP BY customer_id;

5. Materialization behavior in other database systems

SQL Server fundamentally treats CTEs as plain text substitution, similar to a view, and does not materialize them as an independent intermediate result by default, the optimizer considers the entire query including all CTE definitions as one connected whole. That essentially matches the new PostgreSQL 12 behavior, though without the explicit control via MATERIALIZED or NOT MATERIALIZED that PostgreSQL offers.

MySQL, since introducing CTEs in version 8.0, also primarily follows an inlining approach similar to SQL Server, with one important exception: when a CTE is referenced multiple times within the same query, MySQL still automatically materializes it in certain cases to avoid recomputing it repeatedly. Oracle in turn decides case by case between inlining and materialization based on the optimizer's cost estimate, without developers being able to force that decision explicitly the way PostgreSQL allows, which additionally complicates portable performance predictions across systems.

6. The multiple reference problem in CTE chains

A particularly relevant special case arises when a single CTE within the chain gets referenced multiple times by subsequent CTEs or the main query. On databases with strict inlining behavior, this can cause the underlying logic of the referenced CTE to be fully re evaluated on every reference instead of being computed once and reused multiple times, which can noticeably drive up overall runtime for computationally expensive intermediate steps.

In PostgreSQL 12 and newer, this problem can be solved specifically by explicitly marking exactly that one, multiply referenced CTE as MATERIALIZED, without materializing the entire chain and thereby unnecessarily slowing down other parts of the query that are perfectly fine being inlined. On databases without this explicit control, the only remaining option is often to deliberately move the affected intermediate step into a temporary table instead of a plain CTE, once measurements actually confirm repeated recomputation as the cause.

7. When a CTE chain becomes a genuine performance problem

There is no blanket number for the maximum sensible count of chained CTEs, but recurring warning signs do show up in practice. Once a chain grows beyond six to eight steps, where every step processes substantial data volumes and gets referenced multiple times, it becomes increasingly difficult to intuitively follow the overall plan chosen by the optimizer, even on databases with full inlining. A single poorly chosen join type deep within the chain can then affect all subsequent processing without the cause being apparent from the SQL text alone.

A reliable warning sign in practice is a clear discrepancy between the expected and actual runtime of a long CTE chain, visible in EXPLAIN ANALYZE through significantly diverging estimated and actual row counts at a specific point in the chain. In such cases, splitting the chain into several independent queries with intermediate storage in genuine temporary tables, combined with targeted indexes on the intermediate results, is often worthwhile, rather than maintaining an ever growing single WITH chain.

-- For very long chains: an explicit intermediate step as a temp table
CREATE TEMP TABLE temp_customer_totals AS
SELECT customer_id, COUNT(*) AS order_count, SUM(total_amount) AS total_spent
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '365 days'
GROUP BY customer_id;

CREATE INDEX ON temp_customer_totals (total_spent);

-- Subsequent steps read from the indexed temp table
WITH ranked_customers AS (
    SELECT customer_id, order_count, total_spent,
           PERCENT_RANK() OVER (ORDER BY total_spent) AS spend_percentile
    FROM temp_customer_totals
)
SELECT * FROM ranked_customers WHERE spend_percentile >= 0.9;

8. Readability is not the only decision criterion

Even though readability is a strong argument for CTE chains, it should not be the only criterion when deciding between a WITH chain, a temporary table, and a materialized view. For recurring, computationally expensive intermediate results used by several independent queries within the same system, a genuine, persistent intermediate table with its own refresh cycle is often the better choice, even if that costs some readability compared to an inline formulated CTE chain.

Conversely, for one off, ad hoc analytical queries, a well structured CTE chain is almost always the right choice, because quick comprehensibility and easy adaptability weigh far more heavily here than a performance difference measured in milliseconds. The decision should therefore always be made in the context of actual usage, not as a blanket style rule applied to every query in a project.

9. Best practices for structuring long CTE chains

It has proven useful to restrict each CTE to exactly one clearly delineated business task, such as filtering, aggregating, or enriching, rather than bundling several tasks into a single CTE. This separation not only aids understanding but also targeted debugging of individual steps, since every intermediate CTE can be queried in isolation with a plain SELECT without needing to adjust the rest of the chain.

For particularly long chains, a short comment above every CTE definition summarizing the business task of that step in one sentence is also recommended, similar to a subheading in a longer document. Combined with descriptive CTE names and a deliberate decision on materialization where the database offers that control, a CTE chain remains well traceable for new team members even at six or more steps.

10. Summary

11. FAQ: Chaining CTEs: Key Takeaways