From: <msalais@msym.fr>
To: "'David Rowley'" <dgrowleyml@gmail.com>,
	"'Ba Jinsheng'" <bajinsheng@u.nus.edu>
Cc: "'Andrei Lepikhov'" <lepihov@gmail.com>,
	<pgsql-performance@lists.postgresql.org>
References: 
 <SEZPR06MB649446E1B2745EEF5B49BA238A2E2@SEZPR06MB6494.apcprd06.prod.outlook.com>
 <CAApHDvouVmVBA8KvHuQHjsBS4v3dg9a_hGDQecA2BPYZgNhseQ@mail.gmail.com>
In-Reply-To: 
 <CAApHDvouVmVBA8KvHuQHjsBS4v3dg9a_hGDQecA2BPYZgNhseQ@mail.gmail.com>
Subject: RE: CTE Inline On TPC-DS Query 95
Date: Fri, 27 Dec 2024 12:34:17 +0100
Message-ID: <004c01db5853$44124720$cc36d560$@msym.fr>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AQEOniOzl36TbgmJJiEWEBAmpnbuKgHYCe+PtISnbtA=
Content-Language: fr
UI-OutboundReport: notjunk:1;M01:P0:9wXuB1grOgE=;ix+qv9E48TScRO4bgvCl4jo5+Hk
 maN641k3RSRxgYleii8SmLuZOP+99lctxT9EimwbuI6/BUnoKnf+doSyY16aQfUTQq6OV6rfV
 EYniHIAhYn5n5/f2rLly5LVo8/joIt52x27QbHtwu3LdbIsxcAsQYsTDDNS+NJrWjOva8uYy9
 kia4S/gVnBN3yStSGF9ePIcezhLw9xfd0ksU8oFMrZ+g6ZWZs0wl76S7YO80Mu6t2CBT69E6h
 FSiG+TjdMTH/hjvqqCLQO4Mtv4NxIqRuNAYNrPoI9j0Y19jyP1l7FLhnxU5PVANNDuRUHbIfw
 ZxAjR9X5eVVsnbHZV2CWcyuWxbTtkJs2I+ileuLSDAVh9CbSVKjn17qM9WyefllN4YleIw+tF
 oh8mq3fddTbyKXPp9N749LKzMccp8zlxatV/NzeJGxU2+b2ZEbkW6jszyZPdtTcThcwmk19Oi
 5nGgpVN/4PCfGHRtRAYofGfAtk60e+6NhxJU3a65XTJ5UjIF5E2xIN/K0wy9FuQGes0WZMK6B
 rzB4+kl3oikDvtyhbXFZOmhIZgmh0M/9G0tvXbehjR5ZuGZ5BKyFDA0U1UN/qhSLLcZhNKiaP
 PoCFUqTt6YWrOVA1aqVXcCuNDPtXITEshIVStuzsLETAomoKW7Z5PbVKwKR4mBFQMRcUZzdki
 9gfFtH5WafR+xhr9KRW0REEzq2PZFi07Rh6yoOBMrm2epMNs3WU9pT5HK6fehFK3KcerNQ46s
 pdfykSsAqe/Ly8zcdXj6YYaBpOu4IlGa99sdkQPMEHqo6Y+PdEiEJNszQYasBYiJ+Q5ZR7OVz
 WzZDZ1pRM0nyVy+RZ2MC8N3xbqTdUhGhe7LrXR4Q6HBCR2iyhJpYYfTKyG38zIDwX72+dEIeV
 bIdDjtbSp9OZCkELMwD6+DmkHB6Ikv1vBtxnoyIFf258BuBm19lsere5PXpzfxHXtySpbqztA
 lFmzunrNkd1+10OklF1kKtomt7TsrC4NL3dpXOOwAqGCUh+SBAuKMaFeumHgJV3mU//WhohPU
 mZB0VSr4YSKvUodvbxAJyqxtpLswRUEzZcmODmbWXK1zhha/JGET2j51naTo8VuqVqjNleB7z
 oJ2trIu6c=
Archived-At: 
 <https://www.postgresql.org/message-id/004c01db5853%2444124720%24cc36d560%24%40msym.fr>
Precedence: bulk

Hi,

The choice between inlining and materializing is not a question of =
syntax. It matters if the parent query references the CTE expression =
more than once but this is not a decisive factor...

Sometimes one is better and sometimes the other is best.
In Oracle, the choice is done by COST. This should be the same for =
PostgreSQL. In essence, it is the same thing for views: inlining or =
materializing...

Michel SALAIS


-----Message d'origine-----
De : David Rowley <dgrowleyml@gmail.com>=20
Envoy=C3=A9 : mardi 26 novembre 2024 05:03
=C3=80 : Ba Jinsheng <bajinsheng@u.nus.edu>
Cc : Andrei Lepikhov <lepihov@gmail.com>; =
pgsql-performance@lists.postgresql.org
Objet : Re: CTE Inline On TPC-DS Query 95

On Tue, 26 Nov 2024 at 10:03, Ba Jinsheng <bajinsheng@u.nus.edu> wrote:
> If we specify ``NOT MATERIALIZED`` for the query, the execution time =
is reduced from 1min to 1s due to the usage of inline CTE. It seems =
expected as described in the PostgreSQL documentation. However, from the =
code: =
https://github.com/postgres/postgres/blob/REL_17_STABLE/src/backend/optim=
izer/plan/subselect.c#L939, I understand that this query does not =
contain volatile functions and includes simple predicates. I am =
wondering whether we can relax this condition checking, to enable inline =
CTE for such queries? Because it seems the performance benefit is huge =
on a standard benchmark.

The relevant part of the documentation [1] seems to be "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."

If you're proposing that we change the heuristics for when to inline =
CTEs, then I suggest you go and check the archives for why we made this =
decision. You'll need to prove to us why your newly proposed heuristic =
is better than what we have. That's going to take more than one example =
query to show that your heuristics are better.

However, just to save you from wasting any time, I highly doubt an =
adjustment to the CTE inlining heuristics alone is enough.  Just imagine =
the CTE evaluation performed a Seq Scan on a 1 billion row table and =
found 1 matching row. Let's assume the remaining part of the query is =
very cheap. It's obviously going to be better to materialise the CTE so =
that we perform the Seq Scan once rather than twice (or however many =
times the CTE is referenced). Adjusting the heuristics here is just =
going to make some queries faster at the expense of making other queries =
slower. That's never going to pass community standards.

If you truly wanted to improve things here, the heuristics would need to =
be swapped out with a cost-based decision. I can tell you now, that =
doing that is a very difficult task as it basically requires performing =
the join search and quite a large portion of planning once for each =
combination of CTE inlined vs not-inlined. If you had a query with a =
dozen or so CTEs, that's going to be a very costly thing to plan.

David

[1] https://www.postgresql.org/docs/current/queries-with.html