Date: Fri, 27 Nov 2020 14:03:03 -0300
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: "Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
	"pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: Re: CTE materialized/not materialized
Message-ID: <20201127170303.GA14088@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <MW3PR20MB33388940243849D8EA2881C3BEF80@MW3PR20MB3338.namprd20.prod.outlook.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk

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.