3 ms·

> materialized WITH clauses Are you talking about CTEs? And if so, are you suggesting that each "temp table" in the CTE is calculated/materialized a step at a

• jdmichal 5y ago
  Not if you use `MATERIALIZED`: https://www.postgresql.org/docs/14/queries-with.html#id-1.5.6.12.7 https://www.postgresql.org/docs/14/queries-with.html#id-1.5.... > 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. Before PG12 this used to be the only way CTEs worked. Now most CTEs don't need to be materialized, but you can force it and reestablish the optimization barrier.