What this usually means
PostgreSQL versions before 12 (and default behavior in 12+) materialize CTEs as an optimization fence. This means the planner cannot push predicates or join conditions into the CTE, and the entire CTE result is computed and stored as a temporary table. If the CTE returns millions of rows but the outer query only needs a few, you pay the full cost of materialization. The planner also can't use indexes inside the CTE based on WHERE clauses in the outer query. This is the 'optimizer fence' — a deliberate design choice that prevents planning across CTE boundaries, but often kills performance.
The first ten minutes — establish facts before touching code.
- 1Run EXPLAIN (ANALYZE, BUFFERS) on the slow query and look for 'CTE Scan' or 'Materialize' nodes
- 2Compare the row estimate for the CTE node vs actual rows — a large discrepancy indicates poor estimates
- 3Rewrite the CTE as a plain subquery (WITHOUT the WITH) and compare runtimes
- 4Check PostgreSQL version: SELECT version(); — versions < 12 have no inlining
- 5Check if the CTE is referenced more than once; if not, it's likely a candidate for inlining
- 6Set debug_print_plan = on and look for 'NOTICE: CTE materialization forced' in logs
The specific files, logs, configs, and dashboards that usually own this bug.
- searchEXPLAIN (ANALYZE, BUFFERS, TIMING) output — primary diagnostic
- searchPostgreSQL log file (check log_min_duration_statement and log_planner_stats)
- searchpg_stat_statements — find the query with highest total_time
- searchpg_stat_user_tables — check seq_scans vs index_scans for tables in CTE
- searchapplication query layer: check if CTE is generated by an ORM like Sequelize or ActiveRecord
- searchPostgreSQL configuration: check from_collapse_limit and join_collapse_limit
- searchslow query log or pgbadger report
Practical causes, not theory. These are the things you will actually find.
- warningCTE materialized as an optimization fence — planner cannot push predicates into CTE
- warningCTE returns large intermediate result set that is filtered later
- warningMultiple references to the same CTE cause repeated materialization
- warningMissing indexes on tables used inside the CTE for the actual filter conditions
- warningUsing CTEs where a simple subquery or JOIN would suffice
- warningPostgreSQL version < 12 where inlining was not available
Concrete fix directions. Pick the one that matches your root cause.
- buildAdd the MATERIALIZED / NOT MATERIALIZED hint: WITH cte AS NOT MATERIALIZED ( ... ) to force inlining
- buildRewrite CTE as a lateral subquery or plain subquery in the FROM clause
- buildIf CTE is referenced multiple times, consider using a temporary table or explicit materialization
- buildIncrease work_mem to allow hash joins instead of nested loops (for CTE materialization)
- buildAdd indexes on columns used in WHERE clauses that would be pushed into the CTE if inlined
- buildUpgrade to PostgreSQL 12+ and use NOT MATERIALIZED for single-reference CTEs
- buildBreak up the query: compute the CTE result into a temp table, then join
A fix you cannot prove is a guess. Close the loop.
- verifiedRun EXPLAIN (ANALYZE, BUFFERS) after fix: CTE Scan should disappear, replaced by direct table scans or index scans
- verifiedCompare execution time before and after — should see >90% reduction
- verifiedCheck that the row estimates match actual rows closely
- verifiedRun the query multiple times to ensure consistent performance
- verifiedMonitor server CPU and I/O during query execution — should drop significantly
- verifiedVerify that the fix doesn't break other queries that depend on CTE semantics (e.g., recursive CTEs)
Things that make this bug worse or harder to find.
- warningAdding NOT MATERIALIZED to a recursive CTE — it's ignored and can cause infinite loops
- warningAssuming CTEs are always optimized like subqueries — they are not
- warningUsing CTEs for side effects (e.g., UPDATE with RETURNING) as performance optimization
- warningIgnoring the cost of multiple CTE references — each reference triggers materialization
- warningRelying on auto-inlining in Postgres 12+ without checking the plan (auto-inline is conservative)
- warningSetting from_collapse_limit too high — can cause planner explosion
The Dashboard That Took 45 Seconds
Timeline
- 09:15Page load time alert for /reports/daily_sales spikes to 45s
- 09:20Checked slow query log; identified query with two CTEs taking 40s
- 09:25EXPLAIN ANALYZE shows CTE Scan on 'top_products' returning 500k rows, outer query uses only last 10
- 09:30Rewrote first CTE as subquery, runtime drops to 2s
- 09:35Second CTE also materialized; used NOT MATERIALIZED hint (available in PG12 but we were on 11)
- 09:40Upgrade to PG12 was not possible immediately; used temporary table instead
- 09:50Deployed fix: created temp table, populated it, then joined. Runtime 800ms
- 10:00Monitored for 30 minutes; no recurrence
Monday morning, our sales dashboard started timing out. The page was used by every regional manager at 9 AM. I pulled the slow query log and found a monster query with two CTEs that was taking 45 seconds. The query was written by a junior dev who thought CTEs made the SQL 'cleaner'. I ran EXPLAIN ANALYZE and saw that the first CTE, 'top_products', was scanning the entire sales table (500k rows) and joining with products, then the outer query was selecting only the top 10 by revenue. The CTE materialized all 500k rows, then the outer query sorted and limited them. The sort was killing us.
I rewrote the first CTE as a subquery with a LIMIT inside — the planner then pushed the limit down. Runtime dropped to 2 seconds. But there was a second CTE that was referenced twice in the main query. That one needed materialization because of the two references. We were on PostgreSQL 11, so NOT MATERIALIZED wasn't available. I created a temporary table, inserted the CTE result, and then joined to it twice. That brought total runtime to 800ms.
The lesson: CTEs are not performance tools. They're readability tools, and they come with a cost. Always EXPLAIN before deploying a CTE-heavy query. And if you're on an older Postgres, be extra careful.
Root cause
CTE materialization on PostgreSQL 11 forced full materialization of large intermediate result sets, preventing predicate pushdown and causing unnecessary sorting.
The fix
Rewrote the first single-reference CTE as a subquery with LIMIT; replaced the multi-reference CTE with a temporary table.
The lesson
CTEs are materialized by default in Postgres <12; always check the query plan and consider subqueries or temp tables for performance.
PostgreSQL's planner treats CTEs as 'optimization fences' by default. This means the planner does not consider the internal structure of the CTE when planning the outer query. For example, if a CTE returns all rows from a table and the outer query filters on a column, the filter is applied after the CTE result is materialized. This prevents index usage on the filtered column within the CTE.
Starting in PostgreSQL 12, the planner can automatically inline CTEs if they are not recursive, not used in a subquery with side effects, and referenced only once. However, the automatic inlining is conservative. You can force inlining with 'WITH cte AS NOT MATERIALIZED (...)' or force materialization with 'MATERIALIZED'. In versions before 12, CTEs are always materialized.
To check if a CTE is being materialized, look for 'CTE Scan' or 'Materialize' in the EXPLAIN output. If you see 'CTE Scan' on a large node, you are paying the materialization cost.
CTEs shine when the same intermediate result is referenced multiple times. Without a CTE, you'd repeat the subquery, and the planner would execute it each time. With a CTE, the result is materialized once and reused. This can be a net win if the materialization cost is less than the cost of re-executing the subquery.
Recursive CTEs (WITH RECURSIVE) are a special case — they must be materialized by definition. The planner cannot inline them. Performance tuning for recursive CTEs focuses on limiting the recursion depth, using indexes on the recursive step, and ensuring the base case is selective.
Another case: CTEs can act as a 'planning fence' to force a specific join order or to avoid bad plan choices due to poor statistics. This is a deliberate use of the fence for stability, not speed.
1. Run EXPLAIN (ANALYZE, BUFFERS) on the query. Look for 'CTE Scan' nodes and note the actual rows vs estimated rows. A huge discrepancy indicates poor statistics or materialization overhead.
2. Test with a simple subquery: take the CTE body and place it directly in the FROM clause. Compare runtimes. If the subquery is faster, materialization is the issue.
3. Check if the CTE is referenced more than once. If not, force inlining with NOT MATERIALIZED (PG12+) or rewrite as subquery.
4. If multiple references, compare the cost of materialization vs re-execution. Use EXPLAIN (ANALYZE) for the CTE alone to see its execution time. Then multiply by the number of references. If that's less than the materialization cost (CTE Scan time), avoid materialization.
5. Consider using a temporary table for very large CTEs that are referenced multiple times. You can add indexes on the temp table for subsequent joins.
PostgreSQL 11 and earlier: CTEs are always materialized. No NOT MATERIALIZED option. The only fix is rewriting as subquery or using temp tables.
PostgreSQL 12: Introduced NOT MATERIALIZED and MATERIALIZED hints. Automatic inlining is also added for single-reference CTEs that are not recursive and have no side effects.
PostgreSQL 13+: Improved auto-inlining heuristics. More cases are inlined automatically. Still, the explicit hint is the most reliable way.
When migrating from older versions, expect some CTE-heavy queries to speed up automatically. But always verify with EXPLAIN. Some queries may actually slow down if auto-inlining causes poor plans — in that case, add MATERIALIZED to force the fence.
Misconception: 'CTEs are just syntactic sugar for subqueries.' Reality: They change the execution plan due to materialization.
Misconception: 'CTEs are always faster because they avoid repeated computation.' Reality: Only when referenced multiple times and materialization cost is low.
Misconception: 'I can add NOT MATERIALIZED to any CTE.' Reality: It fails for recursive CTEs and those with side effects (e.g., INSERT...RETURNING).
Misconception: 'Upgrading to PG12 fixed all my CTE performance.' Reality: Auto-inlining is conservative; always check the plan.
Frequently asked questions
What does 'optimizer fence' mean for CTEs?
An optimizer fence means the planner treats the CTE as a black box. It cannot push down join conditions, WHERE clauses, or use indexes from outside the CTE inside it. The entire CTE is executed first, materialized as a temporary table, and then the outer query operates on that materialized result. This can lead to poor performance if the outer query filters most rows.
Can I force a CTE to be inlined in PostgreSQL 11?
No. PostgreSQL 11 and earlier always materialize CTEs. You must rewrite the query as a subquery or use a temporary table. The NOT MATERIALIZED hint was introduced in PostgreSQL 12.
When should I use a CTE for performance?
Use a CTE when the same intermediate result is referenced multiple times in the query, and the cost of materializing it once is less than the cost of re-executing the subquery each time. Also use CTEs for recursive queries (WITH RECURSIVE) which require materialization. For single-reference cases, a subquery or lateral join is usually faster.
How do I check if a CTE is being materialized?
Run EXPLAIN (ANALYZE, BUFFERS) and look for 'CTE Scan' or 'Materialize' nodes in the plan. If you see a CTE Scan, the CTE is materialized. You can also check the 'actual rows' vs 'estimated rows' — a large difference often indicates materialization issues.
What is the difference between MATERIALIZED and NOT MATERIALIZED?
MATERIALIZED (the default in PG11 and earlier, optional in PG12+) forces the CTE to be computed and stored as a temporary table. NOT MATERIALIZED (PG12+) tells the planner to inline the CTE's query into the outer query, allowing predicate pushdown and index usage. Use NOT MATERIALIZED for single-reference CTEs that don't have side effects.