Common Table Expressions vs Subqueries in Postgres Performance

Postgres treats CTEs and subqueries differently under the hood, and that difference has flipped at least once in the database's history. Since Postgres 12, the gap between them has narrowed a lot, but it hasn't closed. Knowing exactly where it still exists means writing a query that runs in two seconds instead of one that quietly takes a minute.

Both a CTE and a subquery produce a temporary result set that gets fed into the outer query. On the surface, they look interchangeable. Write the same logic as WITH recent AS (SELECT ...) or as a nested SELECT inside your FROM clause, and you'd expect the database to treat them the same way. For a long time, it didn't. And even now, there are a few sharp edges worth knowing before you pick one over the other out of habit.

A few asymmetries mean CTEs and subqueries aren't perfect substitutes even when performance is identical:

• A CTE can be referenced multiple times in one query. A subquery only exists where you wrote it, once.
• Subqueries can live inside WHERE ... IN (...) and WHERE EXISTS (...). CTEs can't fill that role directly.
• Only CTEs can be recursive. There's no subquery version of a recursive query, full stop.
• CTEs can wrap INSERT, UPDATE, DELETE, or MERGE as auxiliary statements. A subquery in a FROM clause cannot serve that role.

Why Postgres treated CTEs as an optimization fence before version 12

Before Postgres 12, every WITH clause got materialized. No exceptions. The planner would compute the full CTE result, dump it into a temporary buffer (Postgres calls this a tuplestore), and then run the outer query against that buffer. The planner couldn't see through it, or reorganize it, or combine it with anything else.

Take this query: Take this query:

WITH completed AS (
  SELECT * FROM orders WHERE status = 'completed'
)
SELECT * FROM completed WHERE customer_id = 42;

You'd think Postgres would combine both filters and use an index on customer_id right away. Before version 12, it didn't. It computed every completed order first, stored the whole thing, and only then filtered by customer 42. If orders had millions of rows, that's millions of rows fully scanned and buffered before the second filter even runs. The index on customer_id sits there unused.

Write the same logic as a subquery in the FROM clause instead, and the planner could combine both conditions from the start. Same logic, different plan, different runtime.

This wasn't an oversight. Tom Lane, one of the core PostgreSQL developers, called CTEs "optimization fences" in a mailing list post back in 2011, and he was explicit that this was "a feature, not a bug." The idea was that sometimes you want to isolate a piece of a query, force it to run exactly as written, and stop the optimizer from getting clever with it. Materialization gave Postgres a query hint mechanism without needing hint syntax the way another database uses. The trade-off: it mixed up two things that should have been separate: what the query means, and how it gets executed.

CTE inlining by default and the new materialization rules in Postgres 12

Postgres 12 flipped the default. Now, a CTE that's non-recursive, has no side effects, and is only referenced once gets inlined. That means the planner treats it exactly as if you'd never wrapped it in WITH.

Inlining unlocks everything the fence used to block: predicate pushdown, index selection, join reordering. The CTE syntax becomes purely organizational. Write your query with CTEs for readability, and Postgres 12 and later won't punish you for it.

The commit message behind this change said materializing CTE output made sense as a fence for DML and recursive CTEs, but for a plain, single-use, side-effect-free CTE, there was no reason to block the planner from pushing filters down. Nothing about correctness required the fence in those cases. It was just how the code had always worked.

Want to see it for yourself? Run EXPLAIN on that same completed-orders query in Postgres 12 or later. The CTE node vanishes from the plan. What you'll see instead is the customer_id filter showing up as an Index Cond or Filter directly on the orders table scan, exactly as if you'd written it as a subquery from the start.

The MATERIALIZED and NOT MATERIALIZED keywords: overriding the planner when you need to

Postgres 12 didn't just change the default. It also gave you two keywords to override it directly.

• WITH cte AS MATERIALIZED (...) forces the old pre-12 behavior. The CTE gets computed once, stored, and scanned from that stored copy, regardless of how many times it's referenced or what the planner thinks about inlining.
• WITH cte AS NOT MATERIALIZED (...) forces inlining even in cases where Postgres would otherwise materialize by default, like a CTE referenced more than once.

One caveat, straight from Tom Lane: NOT MATERIALIZED is a request, not a guarantee. Certain semantic or implementation constraints can block inlining even when you ask for it. Treat it as a strong preference you're handing the planner.

So when should you reach for MATERIALIZED explicitly?

• The CTE is expensive to compute and gets referenced more than once. Materializing means you pay that cost one time, not once per reference.
• The planner is producing a bad plan when the CTE gets inlined. Materializing forces Postgres to treat the CTE's output as a fixed intermediate result with its own statistics, which sometimes leads to a smarter plan overall.
• You're deliberately using the CTE the old way, as a hint to isolate how a piece of the query gets evaluated.

And NOT MATERIALIZED?

• A multi-reference CTE is getting materialized by default, but you have reason to believe inlining (even with the recomputation cost) would produce a better plan.
• The CTE's result set is small enough that the overhead of writing to and reading from a tuplestore costs more than just recomputing it.

Cases where the planner still gets it wrong on modern Postgres

The Postgres 12 defaults are better on average. "On average" is doing real work in that sentence, though, because the planner is still making a bet based on statistics and cost estimates, and bets don't always pay off.

One case reported on the PostgreSQL bug list involved a production table with 42 million rows, running on Postgres 15/16. A non-materialized CTE triggered a sequential scan that took close to a minute. Adding MATERIALIZED explicitly changed the plan to an index-only scan, and the same query finished in a couple of seconds. The root issue: the optimizer badly overestimated the cost of a Nested Loop join, which pushed it toward the slower plan.

A separate case, reported on the Postgres mailing list, involved a version upgrade from PG 11 to PG 15. A query that had reliably finished in under ten seconds on PG 11 ballooned to several minutes after the upgrade. The fix, again, was adding MATERIALIZED back to the CTE explicitly. The new default inlining behavior had produced a plan that Postgres priced wrong.

Two takeaways here. First, the new defaults aren't a guarantee of a faster query, just a better average outcome. Second, when a query gets meaningfully slower after an upgrade and CTEs are involved, testing MATERIALIZED is one of the first things to try, even if it feels like going backward.

A related but separate hazard: correlated subqueries. A scalar subquery sitting in your SELECT list runs once per outer row. That's an O(n²) pattern, and it's completely invisible just by reading the SQL. EXPLAIN labels it as SubPlan N. That label is a reliable tell that per-row work is happening behind the scenes, and it's almost always worth rewriting as an aggregating JOIN or a correlated aggregate inside a LATERAL clause. Both scale linearly as your outer set grows, instead of multiplying.

Indexes matter enormously here too. A LATERAL join on a user_id column runs fast specifically because there's an index backing that column. Dropping the index multiplies the per-row cost across every single row in the outer query. The join pattern alone doesn't save you if the underlying column isn't indexed.

The multi-reference CTE as a genuine performance tool

Reference a CTE more than once in the same query, and Postgres materializes it by default, even in version 12 and later. This is one case where materialization is exactly the right call, not a fallback.

Think about what a subquery does if you write the same logic twice in one query: it runs twice. Two separate executions, two separate costs. A materialized CTE computes the result once and hands out the same buffered copy at every reference point.

That's a real win when the CTE does something expensive, like a large aggregation or a multi-way join, and the result set is small enough to sit comfortably in memory. Compute once, reuse twice (or more), save the difference.

The trade-off is the same one from before: a materialized result is frozen. The planner can't push predicates back into it from either reference site. So the benefit of computing once has to outweigh the loss of predicate pushdown at both usage points. For small, expensive, reused calculations, that math works out easily. For large or cheap ones, it might not.

EXISTS, IN, and correlated subqueries: patterns where subquery form still matters independently of CTEs

Some patterns simply require subquery syntax. There's no CTE version of WHERE ... IN (...) or WHERE EXISTS (...). This isn't a performance nuance, it's a hard syntactic wall.

On the performance side, EXISTS and IN (SELECT ...) are usually treated identically by the Postgres planner. EXISTS typically produces a semi-join plan, and in practice the planner usually treats IN similarly, so picking between them rarely changes your runtime.

One exception matters a lot: NOT IN with a nullable column is not the same as NOT EXISTS. If even one value in the inner set is NULL, NOT IN returns an unknown result, so no rows come back. It's a subtle trap. Unless you can prove the column is NOT NULL, reach for NOT EXISTS instead.

You could also express "filter by related rows" using an explicit JOIN. But a JOIN produces one output row per match, so duplicates appear if a row matches more than once, and you'll need DISTINCT to clean that up. DISTINCT adds overhead, and for a plain existence check, that overhead usually isn't worth it compared to EXISTS.

A simple rule covers most of this ground: use EXISTS when the question is "does a related row exist." Use a JOIN when you actually need columns back from that related table. Use an aggregating JOIN or LATERAL when what you actually want is a count or some other aggregate.

Recursive CTEs: the one case where CTEs are strictly more capable than subqueries

Hierarchical data breaks the subquery model completely. Org charts, folder structures, product category trees, bills of materials, comment threads that reply to replies, all of these have a depth you can't know in advance. A subquery has to be written with a fixed structure, so it can't walk a tree of unknown depth.

WITH RECURSIVE solves this by iterating: start from a base case, keep adding rows based on the previous pass, and stop once no new rows show up. It's a loop, written in plain SQL.

There's no subquery equivalent to fall back on here. This isn't about one being faster than the other, it's a genuine capability gap. If the data is hierarchical and the depth isn't fixed, a recursive CTE is the only tool in the SQL toolbox built for the job.

Recursive CTEs always materialize each iteration, and that's correct behavior, not a missed optimization. Every pass depends on the result of the one before it, so there's nothing to inline. NOT MATERIALIZED doesn't mean anything useful in a recursive context, since every iteration depends on the previous one.

A practical decision framework for choosing between CTEs and subqueries in Postgres

Start with what the query actually needs to do, not which syntax looks cleaner:

Need to walk a hierarchy of unknown depth? Use a recursive CTE. There's no other option. Need to reuse the same computed result more than once in a query, and it's expensive to compute? Use a CTE, and expect Postgres to materialize it automatically. That's the right outcome here. Writing an existence check, IN, or a correlated filter? Use a subquery. CTEs can't fill that syntactic slot. Writing a simple, single-use filter or transformation? Either form works about the same on Postgres 12 and later, since the CTE gets inlined anyway. Pick whichever reads more clearly. Named, top-level CTEs tend to make long queries easier to follow. Query slower than expected, and a CTE is involved? Look at EXPLAIN. Look for whether the CTE got inlined or materialized, and test the other keyword explicitly. Sometimes the planner's default guess is wrong, and forcing the other behavior fixes it in seconds. Seeing SubPlan N in an execution plan? That's a correlated subquery running once per outer row. Rewrite it as a JOIN or a LATERAL, and make sure the columns involved are indexed.

Whether to use a CTE or subquery used to come with a real performance tax attached, automatically, no way around it. Postgres 12 mostly erased that tax for the common cases. What's left is a smaller, sharper set of situations where the choice still matters: multi-reference CTEs, correlated subqueries, and recursive structures. Learn those three, and the rest of the decision comes down to what reads clearly to the next person who opens the query.