public inbox for [email protected]  

help / color / mirror / Atom feed

From: David Rowley <[email protected]>
To: Ba Jinsheng <[email protected]>
Cc: Andrei Lepikhov <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: CTE Inline On TPC-DS Query 95
Date: Tue, 26 Nov 2024 17:03:24 +1300
Message-ID: <CAApHDvouVmVBA8KvHuQHjsBS4v3dg9a_hGDQecA2BPYZgNhseQ@mail.gmail.com> (raw)
In-Reply-To: <SEZPR06MB649446E1B2745EEF5B49BA238A2E2@SEZPR06MB6494.apcprd06.prod.outlook.com>
References: <SEZPR06MB649446E1B2745EEF5B49BA238A2E2@SEZPR06MB6494.apcprd06.prod.outlook.com>

On Tue, 26 Nov 2024 at 10:03, Ba Jinsheng <[email protected]> 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

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: CTE Inline On TPC-DS Query 95
  In-Reply-To: <CAApHDvouVmVBA8KvHuQHjsBS4v3dg9a_hGDQecA2BPYZgNhseQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox