5

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?

Vérace
  • 854
  • 10
  • 41
Zeruno
  • 1,391
  • 2
  • 20
  • 39

1 Answers1

1

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.

steve
  • 5,870
  • 1
  • 21
  • 22