Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-sql-owner+archive@lists.postgresql.org>)
	id 1kifmA-0004aR-8Z
	for pgsql-sql@arkaria.postgresql.org; Fri, 27 Nov 2020 15:35:10 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-sql-owner+archive@lists.postgresql.org>)
	id 1kifm9-0007Sd-6X
	for pgsql-sql@arkaria.postgresql.org; Fri, 27 Nov 2020 15:35:09 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1kifm9-0007SW-0M
	for pgsql-sql@lists.postgresql.org; Fri, 27 Nov 2020 15:35:09 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1kifm6-0001XS-Vf
	for pgsql-sql@lists.postgresql.org; Fri, 27 Nov 2020 15:35:08 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
	by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 0ARFZ2Sv704896;
	Fri, 27 Nov 2020 10:35:03 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com>
cc: Alvaro Herrera <alvherre@alvh.no-ip.org>,
        "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: Re: CTE materialized/not materialized
In-reply-to: 
 <MW3PR20MB333805C074F3F28E972E0D6FBEF80@MW3PR20MB3338.namprd20.prod.outlook.com>
References: 
 <MW3PR20MB3338A498F87D99C38C645006BEF90@MW3PR20MB3338.namprd20.prod.outlook.com>
 <20201127145347.GA17941@alvherre.pgsql>
 <MW3PR20MB333805C074F3F28E972E0D6FBEF80@MW3PR20MB3338.namprd20.prod.outlook.com>
Comments: In-reply-to "Voillequin,
 Jean-Marc" <Jean-Marc.Voillequin@moodys.com>
	message dated "Fri, 27 Nov 2020 15:18:31 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <704894.1606491302.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 27 Nov 2020 10:35:02 -0500
Message-ID: <704895.1606491302@sss.pgh.pa.us>
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Precedence: bulk

"Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com> writes:
> Let me rephrase.
> In the case the CTE can be inlined, why PG does not compute 2 plans (P1 =
with CTE inlined, P2 with CTE materialized) and compare them to choose the=
 best?

It would be excessively expensive (even for just one CTE, never mind
several) and it wouldn't fit into the planner's structure very well.
As noted in the comment Alvaro quoted, the point where we make these
decisions is pretty far upstream of having any concrete cost estimates.

			regards, tom lane