PostgreSQL CTEs vs Subqueries Performance Guide

When developers ask whether CTEs or subqueries are faster in PostgreSQL, they are usually asking the right question in the wrong way.

Because the real answer is not:

• “CTEs are faster”
• or “subqueries are faster”

The real answer is:

• “What plan does PostgreSQL produce for this specific query shape?”

That distinction matters.

There was a long period in PostgreSQL history where many developers learned a simple rule:

• CTEs can act like optimization fences
• so subqueries are often faster

That rule is now too crude to use blindly.

In modern PostgreSQL, non-recursive, side-effect-free CTEs can often be folded into the parent query, which means they may perform very similarly to equivalent subqueries. But that does not mean CTEs and subqueries are interchangeable in every case. Their behavior still differs when:

• the CTE is referenced more than once
• you force MATERIALIZED
• you use NOT MATERIALIZED
• the query is recursive
• the subquery is correlated
• or the subquery type itself changes how the planner thinks about startup cost and row counts

This guide explains the real performance trade-offs.

Quick Answer

If you want the shortest practical answer:

• use a CTE when you need:

  • readability
  • reuse
  • recursion
  • or deliberate materialization

• use a subquery when it expresses the logic more directly and you do not need a named intermediate step

• do not assume either is faster by default

• use EXPLAIN ANALYZE to verify the actual plan

That is the honest answer.

What Counts as a CTE vs a Subquery?

CTE

A CTE is a WITH query:

with recent_orders as (
  select *
  from orders
  where created_at >= now() - interval '30 days'
)
select *
from recent_orders
where account_id = 42;

It gives a name to an intermediate result.

Subquery

A subquery is a query nested inside another query.

Derived-table subquery in FROM

select *
from (
  select *
  from orders
  where created_at >= now() - interval '30 days'
) recent_orders
where account_id = 42;

Scalar subquery

select
  u.id,
  (
    select count(*)
    from orders o
    where o.user_id = u.id
  ) as order_count
from users u;

EXISTS subquery

select *
from users u
where exists (
  select 1
  from orders o
  where o.user_id = u.id
);

These are all subqueries, but they do not behave identically.

The Old CTE Performance Myth

A lot of PostgreSQL advice still repeats the older rule:

• CTEs are slower because they always materialize

That is no longer a safe general statement.

PostgreSQL’s current docs explain that a non-recursive, side-effect-free CTE can be folded into the parent query when it is referenced only once. That means PostgreSQL can optimize across the boundary instead of treating it as a hard fence in the older style. :contentReference[oaicite:1]{index=1}

So the right modern rule is:

CTEs are not automatically optimization fences anymore.
But they can still become one, either by planner choice or by explicit MATERIALIZED behavior.

That is a much better mental model.

When a CTE Behaves Like an Inline Subquery

A side-effect-free CTE that:

• is non-recursive
• is a plain SELECT
• and is referenced once

can often be folded into the parent query.

That means this:

with filtered_orders as (
  select *
  from orders
  where created_at >= now() - interval '30 days'
)
select *
from filtered_orders
where account_id = 42;

may behave much like this:

select *
from (
  select *
  from orders
  where created_at >= now() - interval '30 days'
) filtered_orders
where account_id = 42;

If PostgreSQL folds the CTE, the planner can push predicates around and optimize more globally.

This is why a lot of blanket “never use CTEs for performance-sensitive SQL” advice is outdated.

When CTEs Still Act More Like a Barrier

CTEs can still behave differently from subqueries when:

• they are referenced multiple times
• you force MATERIALIZED
• they are recursive
• they are not side-effect-free
• they contain volatile behavior that should not be duplicated

PostgreSQL’s docs explain that a WITH query is normally evaluated only once when referenced multiple times, and that MATERIALIZED can be used to force that behavior explicitly. They also explain that NOT MATERIALIZED can force folding when the CTE is eligible. :contentReference[oaicite:2]{index=2}

That is where the real performance differences start to matter.

MATERIALIZED vs NOT MATERIALIZED

This is one of the most important modern PostgreSQL performance controls around CTEs.

MATERIALIZED

Use MATERIALIZED when you want the CTE to be evaluated once and stored as an intermediate result for the rest of the query.

Example:

with materialized expensive_step as materialized (
  select account_id, sum(total_cents) as total_spend
  from orders
  group by account_id
)
select *
from expensive_step
where total_spend > 100000;

Why you might want this:

• the CTE is referenced more than once
• the computation is expensive and should not be repeated
• you deliberately want an optimization fence
• you want stable once-per-query evaluation behavior

NOT MATERIALIZED

Use NOT MATERIALIZED when you want PostgreSQL to treat the CTE more like something that should be folded into the parent query.

Example:

with filtered_orders as not materialized (
  select *
  from orders
  where created_at >= now() - interval '30 days'
)
select *
from filtered_orders
where account_id = 42;

Why this can help:

• parent filters can push deeper into the CTE
• joins can optimize across the boundary
• PostgreSQL can avoid materializing a large intermediate result unnecessarily

Practical rule

• MATERIALIZED is often about control and reuse
• NOT MATERIALIZED is often about joint optimization

When a Subquery Is the Better Choice

Subqueries are often better when the logic is:

• simple
• local to one part of the query
• not reused elsewhere
• and more naturally expressed inline

Examples:

• EXISTS
• scalar lookups
• derived tables in FROM
• simple filters or aggregates used once

A subquery is often the cleaner choice when naming a CTE would just create an extra layer without adding much clarity.

Example:

select *
from users u
where exists (
  select 1
  from orders o
  where o.user_id = u.id
);

Turning that into a CTE usually does not improve performance automatically, and may make the intent less direct.

EXISTS Subqueries Are Often Excellent

EXISTS is one of the best examples of why “subquery” is too broad a category to dismiss.

PostgreSQL’s docs on subquery expressions explain that EXISTS is true if the subquery returns at least one row. The planner and executor can often stop after the first match, which is why EXISTS is frequently a very good performance pattern for “does a related row exist?” logic. The EXPLAIN docs also note that for EXISTS, startup cost can matter more than total cost because the executor can stop early. :contentReference[oaicite:3]{index=3}

That makes this pattern strong:

select *
from users u
where exists (
  select 1
  from orders o
  where o.user_id = u.id
);

And often stronger than awkward aggregation alternatives such as counting everything just to check whether one row exists.

Correlated Subqueries: Powerful, But Easy to Misuse

This is where subqueries can go bad quickly.

A correlated subquery references columns from the outer query:

select
  u.id,
  (
    select count(*)
    from orders o
    where o.user_id = u.id
  ) as order_count
from users u;

This can be perfectly fine in some cases. But it can also become a row-by-row pattern that scales poorly if the planner has to execute it repeatedly for many outer rows.

Why correlated subqueries can be slow

They may cause:

• repeated index lookups per outer row
• repeated aggregations
• nested-loop style execution that becomes expensive at scale
• more work than a join plus grouped aggregate would require

Better alternative in some cases

select
  u.id,
  coalesce(o.order_count, 0) as order_count
from users u
left join (
  select user_id, count(*) as order_count
  from orders
  group by user_id
) o on o.user_id = u.id;

This is not always faster. But it often is for larger result sets.

Practical rule

Correlated subqueries are not automatically bad. But they are one of the first places to check with EXPLAIN ANALYZE if a query scales poorly.

Derived Tables in FROM vs CTEs

A derived table is an inline subquery in FROM:

select *
from (
  select account_id, sum(total_cents) as total_spend
  from orders
  group by account_id
) s
where s.total_spend > 100000;

This is often a fine alternative to a CTE when:

• the result is used once
• the scope is local
• and you do not need the extra readability benefit of naming it with WITH

Performance-wise, a derived table and an inline-folded CTE may end up very similar.

So the better choice often comes down to:

• clarity
• reuse
• and whether you want explicit materialization control

When CTEs Can Be Faster

CTEs can be faster when their behavior fits the workload better than a subquery shape would.

Common cases:

1. Reused intermediate results

If the same expensive intermediate result is needed more than once, evaluating it once can be beneficial.

Example:

with expensive_summary as materialized (
  select account_id, sum(total_cents) as total_spend
  from orders
  group by account_id
)
select *
from expensive_summary s1
join expensive_summary s2 on s1.account_id = s2.account_id
where s1.total_spend > 100000;

2. Preventing a bad plan on purpose

Sometimes MATERIALIZED is useful as an explicit optimization fence when joint planning produces a worse result.

This is more advanced, but it is a legitimate reason to prefer a CTE.

3. Recursive logic

Recursive queries are a major reason to use CTEs. Subqueries do not replace WITH RECURSIVE.

Example categories:

• trees
• org charts
• graph-like hierarchies
• category ancestry
• dependency traversal

In those cases, the CTE is not only a performance choice. It is the correct language feature.

When Subqueries Can Be Faster

Subqueries can be faster when they let PostgreSQL:

• optimize more directly
• stop early
• or avoid unnecessary intermediate materialization

Common cases:

1. EXISTS-style presence checks

These are often excellent because execution can stop early.

2. Inline derived tables used once

If the subquery is simple and local, there may be no reason to introduce a named CTE layer.

3. Cases where materialization would create unnecessary intermediate work

If a large CTE is built and only a small filtered subset is eventually needed, folding may be much better than materializing.

That is exactly the sort of case where NOT MATERIALIZED or a plain inline subquery can help.

A Concrete Performance Example

Imagine you want the newest orders for one account over the last 30 days.

CTE version

with recent_orders as (
  select *
  from orders
  where created_at >= now() - interval '30 days'
)
select id, created_at, total_cents
from recent_orders
where account_id = 42
order by created_at desc
limit 20;

Derived-table version

select id, created_at, total_cents
from (
  select *
  from orders
  where created_at >= now() - interval '30 days'
) recent_orders
where account_id = 42
order by created_at desc
limit 20;

In modern PostgreSQL, these may plan similarly if the CTE is eligible for folding.

But if you force:

with recent_orders as materialized (
  ...
)

then PostgreSQL may have to build the 30-day intermediate result first and only then filter for one account.

That can be meaningfully slower if the 30-day set is large.

CTEs for Readability Are Still Worth Using

Performance is not the only consideration.

One reason CTEs remain valuable is that they make large queries easier to:

• read
• review
• debug
• and maintain

Example:

with active_accounts as (
  select id
  from accounts
  where status = 'active'
),
recent_orders as (
  select *
  from orders
  where created_at >= now() - interval '30 days'
),
account_spend as (
  select o.account_id, sum(o.total_cents) as total_spend
  from recent_orders o
  group by o.account_id
)
select a.id, s.total_spend
from active_accounts a
join account_spend s on s.account_id = a.id
where s.total_spend > 100000;

This may be much easier to reason about than one deeply nested query.

If PostgreSQL can inline the safe parts anyway, you may get the readability benefit without paying much of a performance price.

The Best Performance Rule: Measure the Actual Plan

This is one of the clearest areas where EXPLAIN ANALYZE matters.

Do not choose between CTE and subquery based on style debates alone. Check:

• whether the CTE was folded
• whether the query materialized a large intermediate result
• whether the correlated subquery became row-by-row work
• whether EXISTS stopped early as expected
• whether join order and filtering changed between versions

Good workflow

1. write the clearest version first
2. run EXPLAIN ANALYZE
3. inspect:
   • scan types
   • row estimates
   • sort nodes
   • materialization behavior
   • join choices
4. rewrite only if the plan shows a real problem

That is usually better than prematurely flattening every query.

Common Mistakes

1. Assuming all CTEs are optimization fences

That is outdated for modern PostgreSQL.

2. Assuming subqueries are always faster

They are not. The plan still decides.

3. Forcing MATERIALIZED without a reason

This can create a large intermediate result that the parent query did not really need.

4. Using correlated subqueries carelessly

These can scale poorly if executed once per outer row.

5. Rewriting readable CTEs into messy nested SQL without measuring

This often makes the query harder to maintain without producing a real gain.

6. Ignoring EXISTS

For presence checks, EXISTS is often one of the best patterns available.

Practical Rules of Thumb

Use these rules as a starting point:

• prefer a CTE when:

  • the query becomes meaningfully easier to understand
  • you need recursion
  • you want to reuse an intermediate result
  • or you deliberately want MATERIALIZED

• prefer a subquery when:

  • it is local and used once
  • it fits naturally as EXISTS, scalar lookup, or inline derived table
  • or naming a CTE would add structure without much value

• be extra careful with:

  • correlated subqueries
  • forced materialization
  • huge intermediate result sets

• always validate with:

  • EXPLAIN ANALYZE

Conclusion

The real PostgreSQL answer to CTEs vs subqueries is:

Neither is universally faster.
The planner, the query shape, and the specific purpose decide the performance outcome.

In modern PostgreSQL:

• CTEs are not automatically slow
• subqueries are not automatically better
• MATERIALIZED and NOT MATERIALIZED matter
• EXISTS is often excellent
• correlated subqueries deserve close inspection
• and readability is still a valid reason to use a CTE when the plan stays good

That means the best practical rule is simple:

Write the clearest correct query first. Then use EXPLAIN ANALYZE to see whether PostgreSQL agrees with your structure. If it does not, adjust the shape with evidence, not folklore.