🔗 Good CTE, bad CTE

CTEs (Common Table Expressions) are a popular feature among SQL developers, often used to simplify complex queries. However, their actual performance and optimizations have significantly changed in PostgreSQL 12, shifting from always being treated as optimization fences to more flexible handling of CTEs.

PostgreSQL 12 introduced several optimizations regarding CTE handling, allowing non-recursive, side-effect-free CTEs to be inlined and treated as subqueries. This change improves query performance by enabling predicate pushdown and better index usage, while also providing control over CTE materialization when needed for specific use cases.