Since Postgres 12, a CTE is folded into the query that uses it, like a subquery, as long as it is referenced once, is not recursive, and has no side effects. Before 12, every CTE was computed and stored first, which is where “CTEs are slow in Postgres” comes from.
with recent as (
select * from orders where created_at > now() - interval '90 days'
)
select * from recent where customer_id = 4242;On 2 million orders, Postgres 16 combines both conditions and uses the index on customer_id: 0.16 ms. Write with recent as materialized (...) and it builds all 164,398 recent orders first, then filters: 138 ms.
A CTE referenced twice is materialized by default, so the expensive part runs once. NOT MATERIALIZED overrides that. A volatile function such as random() inside the CTE also keeps it materialized. now() is stable, so it does not.
To see which one you got, run EXPLAIN: a materialized CTE shows up as a CTE Scan, and an inlined one is not in the plan at all.