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 MATERIALIZED forces computation once (good for expensive calculations referenced multiple times)
  • AS NOT MATERIALIZED allows 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