> The downside is that a PostgreSQL version update can change internal optimizer rules This has actually happened relatively recently. PostgreSQL Common Table

> The downside is that a PostgreSQL version update can change internal optimizer rules This has actually happened relatively recently. PostgreSQL Common Table Expressions (WITH x as (some query)) used to be optimization fences by default, meaning that you can influence the optimizer using a CTE. This is a well known technique among PostgreSQL users that resort to it for lack of optimizer hints. In Pg12 they enhanced the optimizer to remove the optimization fence by default, so the query plans of existing queries automatically changed and sometimes became much slower as a result. If you want the old CTE behavior you have to modify the WITH clause via AS MATERIALIZED.