Good CTE, Bad CTE (boringsql.com)

The article explains how PostgreSQL treats Common Table Expressions (CTEs), arguing that the long-standing advice “CTEs are optimization fences” is outdated since PostgreSQL 12. It breaks down when CTEs are inlined versus materialized (e.g., single-use read-only CTEs inline; multi-reference, recursive, data-modifying, and volatile-function CTEs materialize), and covers related effects like statistics propagation and edge cases that can still impact performance. The overall message is to choose CTE style deliberately and use MATERIALIZED/NOT MATERIALIZED hints when needed.