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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.