CTEs and the optimizer

Question

Accepted Answer

Before PostgreSQL 12, all CTEs were 'optimization fences' - the planner always materialized them and could not push predicates into them. From PostgreSQL 12 onward, the planner inlines non-recursive CTEs by default, allowing predicate pushdown and other optimizations. To force materialization (which can be useful if the CTE is expensive and referenced multiple times), use WITH cte AS MATERIALIZED (...). This change surprised developers who relied on the old fence behavior to avoid re-evaluation.