When does the PostgreSQL 12 CTE materialized option provide an optimization advantage?

user_7290845 0
Viewed 857

EDIT: I have moved this question over to dba.stackexchange as I have received advice that that community can better address my question.

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?

postgresql-12 common-table-expression postgresql
1 Answers

What it refers to is that a inlined subquery can lead to slow queries and a CTE (which operates as an optimization fence avoids that). Here is an example bug for this.

user_1210350
Related