PostgreSQL Enterprise & Performance
8-10 Years (Tech Lead)
Hard
Production Scenario
Explain CTE Optimization Fencing (`AS MATERIALIZED` vs `AS NOT MATERIALIZED`) in PostgreSQL 12+.
Real-World Scenario / Incident Description
A complex query using Common Table Expressions ran 10x slower because the optimizer materialized intermediate result sets.
212 views
•
Updated 1 month ago
Controlling CTE Inlining
WITH my_cte AS NOT MATERIALIZED (...): Forces the query planner to inline the CTE directly into the parent query, allowing index pushdown and filter sharing.WITH my_cte AS MATERIALIZED (...): Forces PostgreSQL to compute the CTE once into a temporary buffer, preventing repetitive recalculation if referenced multiple times.
Related Concepts:
#CTE
#MATERIALIZED
#Query Optimizer
More PostgreSQL Enterprise & Performance Interview Questions
How does PostgreSQL Multi-Version Concurrency Control (MVCC) manage Tuple Visibility (`xmin`, `xmax`), and why does Table Bloat happen?
Read →
How do GIN (Generalized Inverted Index) and GiST indexes optimize high-speed JSONB queries and Full-Text Search in PostgreSQL?
Read →
How does PgBouncer Connection Pooling operate in Transaction Mode vs. Session Mode, and what PostgreSQL features break in Transaction Mode?
Read →
When should you use BRIN (Block Range Index) over B-Tree indexes for massive Time-Series and Log tables?
Read →
How do PostgreSQL Advisory Locks enable distributed cross-server concurrency control without external Redis clusters?
Read →