Part 90: Materialized vs Not Materialized CTEs (Optimization Fences)

How the query optimizer handles CTE execution under the hood.

1. The Optimization Fence

• AS MATERIALIZED: Forces the database to execute the CTE once, write it to memory/disk, and prevent the optimizer from inlining filters.
• AS NOT MATERIALIZED: Tells the optimizer to inline the CTE directly into the main query for predicate pushdown.

WITH ExpensiveQuery AS NOT MATERIALIZED (
  SELECT * FROM heavy_events
)
SELECT * FROM ExpensiveQuery WHERE event_id = 99;

Key Takeaways

• Use NOT MATERIALIZED when you want outer filters pushed down into the CTE for index usage.

In Part 91, we enter Module 10 to master Window Functions (Analytical SQL)!