4 ms·
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 i
by vector_spaces 3y ago
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?
- aarondf 3y agoWhat is an optimization fence?
- simcop2387 3y agoThe database and query planner can't look past it to see that it can simplify the operations tthat the query will do.
- orangepanda 3y agoI 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.
- zomgwat 3y agoAs of PostgreSQL 12, whether the optimization fence is used or not is controlled with MATERIALIZED and NOT MATERIALIZED.
- srcreigh 3y agoBy 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.