Intermediate LevelPostgreSQL
How does AS MATERIALIZED vs AS NOT MATERIALIZED control CTE query optimization in Postgres 12+?
❓ Interview Prompt: Explain Common Table Expressions (CTEs), `WITH` clause, CTE optimization fences, `AS MATERIALIZED` (caching CTE), and `AS NOT MATERIALIZED` (inlining).
Prior to PostgreSQL 12, CTEs ('WITH' queries) acted as optimization fences, executing as isolated temporary tables.
Postgres 12+ CTE Control:
•
'AS NOT MATERIALIZED' (Default): Inlines the CTE query into the main query, allowing the optimizer to push down 'WHERE' clauses and use indexes!
•
'AS MATERIALIZED': Forces PostgreSQL to evaluate the CTE query ONCE and store the result in memory (useful if the CTE is referenced multiple times).
Code Implementation Example
sql
-- Force CTE inlining to allow index pushdown optimization
WITH recent_orders AS NOT MATERIALIZED (
SELECT id, customer_id, total_amount FROM orders WHERE status = 'COMPLETED'
)
SELECT * FROM recent_orders WHERE customer_id = 42;💡Controlling CTE inlining using AS NOT MATERIALIZED.
🎯 Interview Key Takeaway
Use AS NOT MATERIALIZED in Postgres 12+ to inline CTEs and enable query optimizer index pushdown.
#CTE#MATERIALIZED#Optimization#WITH#Intermediate
Back to all PostgreSQL Questions→