Re: CTE materialized/not materialized

On 2020-Nov-27, Voillequin, Jean-Marc wrote:

> Thank you Tom & Alvaro.
> I'm still dreaming of such query planner!

An idea mentioned several times is that of making more expensive
optimizer passes for certain plans -- either at the user's request or
merely based on the initial estimated cost being very large. A query
that's seems more expensive to execute would also seem to warrant a
greater optimization effort. (But if it's automated and we get it
wrong, then we'd get complaints that we spend pointless time in the
optimizer.)

Your sample plan has 3 CTEs, so we would have to plan eight queries for
the brute-force way -- one per combination of each CTE inlined or
materialized. Maybe it's worth the optimizer cost, maybe not. Now
maybe there's a smarter approach than brute-forcing it.