Postgres WITH Optimization Fence
Came across this brief and insightful exchange in Hacker News comments.
My understanding is that CTEs are an optimization fence in some databases so aren’t great for web application queries? I think that this is no longer the case in Postgres, but I recall learning that like ~6 years ago when working with other databases. Or is that total nonsense?
As of PostgreSQL 12, whether the optimization fence is used or not is controlled with MATERIALIZED and NOT MATERIALIZED.
By default it’s an optimization fence if a CTE is referenced 2+ times, and not an optimization fence if referenced 0-1 times. The MATERIALIZED and NOT MATERIALIZED overrides the default.
What is an optimization fence?
The database and query planner can’t look past it to see that it can simplify the operations that the query will do.