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 <dgrowle...@gmail.com> 
Envoyé : mardi 26 novembre 2024 05:03
À : Ba Jinsheng <bajinsh...@u.nus.edu>
Cc : Andrei Lepikhov <lepi...@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 <bajinsh...@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/optimizer/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




Reply via email to