David Fetter <david@fetter.org> writes:
> It might be worth documenting the fact that NOT MATERIALIZED doesn't
> affect DML CTEs, just as it doesn't affect statements with volatile
> functions and recursive CTEs.
We already do:
However, if a WITH query is non-recursive and side-effect-free (that
is, it is a SELECT containing no volatile functions) then it can be
folded into the parent query, allowing joint optimization of the two
query levels. By default, this happens if the parent query references
the WITH query just once, but not if it references the WITH query more
than once. You can override that decision by specifying MATERIALIZED
to force separate calculation of the WITH query, or by specifying NOT
MATERIALIZED to force it to be merged into the parent query. The
latter choice risks duplicate computation of the WITH query, but it
can still give a net savings if each usage of the WITH query needs
only a small part of the WITH query's full output.
regards, tom lane