pg_cte_force — Controlling CTE materialization in PostgreSQL
Starting with PostgreSQL 12 , the behavior of Common Table Expressions (CTEs) changed significantly. Before PostgreSQL 12, CTEs acted as an optimization fence and were always materialized. Since PostgreSQL 12, the planner can inline side-effect-free CTEs when appropriate. By default, a CTE referenced only once can be folded into the parent query, while a CTE referenced multiple times is normally materialized. PostgreSQL also provides the MATERIALIZED and NOT MATERIALIZED clauses to explicitly control this behavior. This is generally a great improvement for query optimization. But what happens when you have to deal with legacy applications ? I often work with applications originally developed for PostgreSQL versions earlier than 12, where queries were written assuming the old CTE behavior. Sometimes things become even more complicated because SQL queries are generated by frameworks or applications that cannot easily be modified to add the MATERIALIZED clause. So I decided to wr...
