Senior LevelSQL & MySQL
How do modern SQL Optimizers handle CTE Inlining versus Derived Table Materialization?
❓ Interview Prompt: Explain CTE optimization, CTE Inlining (MySQL 8 / PostgreSQL 12+), `MATERIALIZED` vs `NOT MATERIALIZED` hints, and predicate pushdown.
Older database engines always materialized CTEs into temporary tables on disk. Modern query engines optimize CTEs intelligently:
Inlining & Optimization:
•
CTE Inlining: The optimizer merges non-recursive CTE definitions directly into the main query Abstract Syntax Tree (AST), applying Predicate Pushdown and index scans across joined tables.
•
Optimization Hints: PostgreSQL 12+ allows explicit control: 'WITH cte AS MATERIALIZED (...)' or 'NOT MATERIALIZED'.
Code Implementation Example
sql
-- Explicitly controlling CTE materialization in PostgreSQL 12+
WITH filtered_orders AS NOT MATERIALIZED (
SELECT user_id, total_amount FROM orders WHERE created_at >= '2026-01-01'
)
SELECT u.email, f.total_amount
FROM users u
JOIN filtered_orders f ON u.id = f.user_id;💡Enforcing CTE inline optimization with NOT MATERIALIZED hint.
🎯 Interview Key Takeaway
Rely on modern query optimizers to inline CTEs, using hints to control materialization when needed.
#SQL#CTE#Optimizer#Materialization#Senior
Back to all SQL & MySQL Questions→