Hacker News Reader

What is an optimization fence?

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

I most commonly use CTEs for splitting ranges into individual values (1-5 into 1,2,3,4,5). It’s an order of magnitude faster than joining some utility table, which some still recommended.

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.