Why CTE vs Subquery Matters More on Redshift Than Anywhere Else

Maithreyan — Mon, 03 Aug 2026 12:00:00 +0000

On Snowflake or modern Postgres, CTE vs subquery barely matters for performance. On Redshift, it can matter a lot — and I learned this the hard way.

The assumption I carried over

I carried an assumption over from Snowflake: that the optimizer would treat a CTE and an equivalent subquery the same way. Redshift doesn't play by those rules. Redshift's query planner is derived from an older Postgres lineage, and it historically doesn't inline CTEs the way newer engines do — it can materialize them as a separate step before the outer query even runs.

Why this matters on an MPP engine

This matters a lot on Redshift specifically because it's a columnar, MPP (massively parallel processing) engine. Materializing a CTE means writing an intermediate result set across the cluster's compute nodes before the next step even starts. On a small CTE, that's harmless. On a CTE scanning millions of rows from a big fact table, that's a real cost — extra I/O and redistribution across nodes that a well-written subquery or a join might avoid entirely.

What actually happened

I hit this on a Redshift ETL query pulling from a large events table. I had it as a CTE, referenced once in the outer query, nothing complex. When I flattened it into a subquery instead, the query planner pushed filters down earlier and pruned way more data before the expensive join — noticeably faster on that specific table.

My rule of thumb for Redshift now

Use a subquery when it's referenced once and simple — let the planner push predicates down early
Use a CTE mainly for readability on complex multi-step logic, but check EXPLAIN before trusting it won't materialize unnecessarily
Always check the query plan (EXPLAIN) rather than assuming CTE behavior — Redshift doesn't guarantee the same optimizations as Snowflake or Postgres 12+
For genuinely reused logic, a temp table often beats both if the CTE is being scanned multiple times

The takeaway

Patterns that are "safe defaults" on one warehouse can be real performance traps on another. Always validate with EXPLAIN before assuming.