Re:CTEs as Lookup Tables
by masklinn on Thursday January 12, 2023 @01:00AM
(#34349764)
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-mat...
But so can MAT CTEs: https://dba.stackexchange.com/questions/257014/are-there-sid...
So the limitations are that it’s very much ymmv.