On: Lesser-known Postgres features

[dylanz]

It's worth to note that earlier versions of PostgreSQL didn't include the "AS NOT MATERIALIZED" option when specifying CTE's. In our setup, this had huge hits to performance. If we were on a more recent version of PostgreSQL (I think 11 in this case), or if the query writer just used a sub-query instead of a CTE, we would have been fine.

[brasetvik → dylanz]

Yep! A lot of older posts about CTEs largely advice against them for this reason.

Postgres 12 introduced controllable materialization behaviour: https://paquier.xyz/postgresql-2/postgres-12-with-materializ...

By default, it'll _not_ materialise unless it's recursive, or if there are >1 other CTEs consuming it.

When not materializing, filters may push through the CTEs.