Various comments above expand upon it, but pg12 only changed CTEs which are referenced once to default to NOT MATERIALIZED. Multi-referenced CTEs remain materia

Various comments above expand upon it, but pg12 only changed CTEs which are referenced once to default to NOT MATERIALIZED. Multi-referenced CTEs remain materialized by default. Also not-mat CTEs can perform a lot worse: https://stackoverflow.com/questions/64016236/postgres-12-materialized-cte-much-faster https://stackoverflow.com/questions/64016236/postgres-12-mat... But so can MAT CTEs: https://dba.stackexchange.com/questions/257014/are-there-side-effects-to-postgres-12s-not-materialized-directive https://dba.stackexchange.com/questions/257014/are-there-sid... So the limitations are that it’s very much ymmv.