Good CTE, bad CTE
Summary
This article provides an in-depth look at PostgreSQL Common Table Expressions (CTEs), tracing their evolution from an optimization fence to inlining in PG12+, and detailing eight practical cases that influence materialization. It also covers the statistics implications, writable and recursive CTEs, edge cases like partition pruning, plan caching, and improvements introduced in PostgreSQL 17 and 18, ending with actionable guidance on when to inline, materialize, or use alternatives like temporary tables.