gotchasqlpostgresqlModerate
CTEs vs subqueries: optimization fence behavior in PostgreSQL
Viewed 0 times
CTE optimization fenceNOT MATERIALIZEDMATERIALIZEDsubquery vs CTEquery plannerCTE scan
Problem
A complex query rewritten as a CTE for readability suddenly becomes slower than the equivalent subquery because the planner materializes the CTE result and cannot push predicates into it.
Solution
Understand when CTEs act as optimization fences and use NOT MATERIALIZED:
-- PG 11 and earlier: ALL CTEs are optimization fences (materialized).
-- PG 12+: non-recursive CTEs are inlined unless they are volatile or referenced multiple times.
-- Force inlining (PG 12+):
WITH recent_orders AS NOT MATERIALIZED (
SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT * FROM recent_orders WHERE customer_id = 42;
-- Force materialization (evaluate once, good for expensive CTEs referenced multiple times):
WITH expensive_agg AS MATERIALIZED (
SELECT customer_id, sum(amount) AS total FROM orders GROUP BY 1
)
SELECT c.name, e.total
FROM customers c
JOIN expensive_agg e ON e.customer_id = c.id;
-- PG 11 and earlier: ALL CTEs are optimization fences (materialized).
-- PG 12+: non-recursive CTEs are inlined unless they are volatile or referenced multiple times.
-- Force inlining (PG 12+):
WITH recent_orders AS NOT MATERIALIZED (
SELECT * FROM orders WHERE created_at > now() - interval '7 days'
)
SELECT * FROM recent_orders WHERE customer_id = 42;
-- Force materialization (evaluate once, good for expensive CTEs referenced multiple times):
WITH expensive_agg AS MATERIALIZED (
SELECT customer_id, sum(amount) AS total FROM orders GROUP BY 1
)
SELECT c.name, e.total
FROM customers c
JOIN expensive_agg e ON e.customer_id = c.id;
Why
Materialized CTEs compute their result once and store it in a temporary file, preventing the planner from applying outer WHERE conditions inside. Inlined CTEs are substituted into the main query before planning, letting the planner optimize the whole query at once.
Gotchas
- In PG 11 and below every CTE is materialized; avoid CTEs in hot paths on older versions
- Recursive CTEs are always materialized regardless of version or hint
- Using a CTE multiple times in one query forces materialization in PG 12+ even without MATERIALIZED keyword
- EXPLAIN output shows 'CTE Scan' for materialized CTEs and inlines the subplan for NOT MATERIALIZED
Code Snippets
EXPLAIN to verify CTE materialization status
-- Check if CTE is materialized:
EXPLAIN SELECT * FROM (
WITH orders_cte AS (SELECT * FROM orders)
SELECT * FROM orders_cte WHERE customer_id = 1
) t;
-- 'CTE Scan' node = materialized; no CTE Scan = inlinedContext
Complex queries refactored to use CTEs for readability that regress in performance
Revisions (0)
No revisions yet.