I have been reading about PostgreSQL's new CTE feature - the MATERIALIZED or NOT MATERIALIZED keyword - which can offer additional optimization opportunities under certain circumstances, provided that it is safe to do so. A comment on this PostgreSQL update here says:
... the user can force the old behavior [LESS OPTIMIZED] by specifying MATERIALIZED; this would mainly be useful when the query had deliberately been employing WITH as an optimization fence to prevent a poor choice of plan.
I have been wondering, given that there is a situation where it is safe to use the CTE optimization (NOT MATERIALIZED keyword), under which circumstances can the less optimized "MATERIALIZED" keyword "prevent a poor choice of plan" as the quote says or provide an even better plan?
A final note, this is a copy of the question over at https://stackoverflow.com/questions/60701158/when-does-the-postgresql-12-cte-materialized-option-provide-an-optimization-adva. I was given the advice that this question fits this community better and to thus repost here.
