Postgres WITH Optimization Fence

Came across this brief and insightful exchange in Hacker News comments.

vector_spaces:

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?

zomgwat:

As of PostgreSQL 12, whether the optimization fence is used or not is controlled with MATERIALIZED and NOT MATERIALIZED.

srcreigh:

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.

aarondf:

What is an optimization fence?

simcop2387:

The database and query planner can’t look past it to see that it can simplify the operations that the query will do.

---

---

---

---