Hacker News Reader

CTEs used to be "optimization fences" in PostgreSQL, but that changed with v12. https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...

This blog has a VERY high signal to noise ratio for anyone interested in digging into the PostgreSQL internals. Lots of great articles.

They still are sometimes. Whenever you have a CTE which is referenced more than once, to be specific.

Similarly, joining a CTE to itself can also be problematic. In that case materializing to a temp table can be faster.