Guaranteeing a CTE runs only once

Question

Accepted Answer

PostgreSQL 12+ inlines non-recursive CTEs by default, which means the planner might execute the CTE body twice when referenced twice. Adding the MATERIALIZED keyword opts back into the old fence behavior: the result is computed once, stored in memory, and reused. The trade-off is that materialization prevents predicate pushdown from the outer query into the CTE. Use MATERIALIZED when the CTE is expensive and reuse matters more than optimal filtering.