SQLsqlcteoptimizationreadabilityproduction-ready
Materialized CTEs and Query Decomposition
Complex query decomposition using CTEs for readability, performance optimization with materialized CTEs, and query plan control.
title: "Materialized CTEs and Query Decomposition" description: "Complex query decomposition using CTEs for readability, performance optimization with materialized CTEs, and query plan control." category: "SQL" language: "sql" tags: ["sql", "cte", "optimization", "readability", "production-ready"] createdAt: "2025-04-20" updatedAt: "2025-05-05"
Overview
Complex queries decomposed into readable, named steps using CTEs. Each CTE builds on the previous one, making multi-step business logic transparent. Materialization hints control whether the optimizer computes CTEs once or inlines them.
Code
WITH
step1 AS (SELECT ...),
step2 AS (SELECT ... FROM step1),
step3 AS (SELECT ... FROM step2)
SELECT * FROM step3;Notes
- CTEs improve readability by naming each logical step (self-documenting SQL)
AS MATERIALIZEDforces computation once (good for expensive calculations referenced multiple times)AS NOT MATERIALIZEDallows inlining (good for simple filters the optimizer should push down)- Before PostgreSQL 12, all CTEs were optimization fences (always materialized)
- CTEs are not indexed; for large intermediate results, consider temp tables with indexes
- Layered CTEs enable unit testing of each step independently during development