From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrei Lepikhov <lepihov@gmail.com>
Cc: Kirill Reshke <reshkekirill@gmail.com>
Cc: pingw33n@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18751: Sub-optimal UNION ALL plan
Date: Mon, 23 Dec 2024 22:46:58 -0500
Message-ID: <1531450.1735012018@sss.pgh.pa.us> (raw)
In-Reply-To: <e405c80a-49ee-4a37-827d-d53b5189ad3e@gmail.com>
References: <18751-887c7bf4e67e65a7@postgresql.org>
<CALdSSPg3Dvw1oy+AJgUX15hDgFHYkjt7w5gGfVABt1vbSNewUg@mail.gmail.com>
<1347038.1734967136@sss.pgh.pa.us>
<e405c80a-49ee-4a37-827d-d53b5189ad3e@gmail.com>
Andrei Lepikhov <lepihov@gmail.com> writes:
> On 12/23/24 22:18, Tom Lane wrote:
>> Yeah. I believe what is happening is that the addition of the WHERE
>> clause forces the second sub-SELECT to be planned as an independent
>> query. And that level of planning has no idea that it might be
>> useful to produce a result ordered by "t", so it doesn't generate
>> a sub-plan that can do that. Then the best that the outer level
>> can do is sort after-the-fact.
> I didn't discover the case deeply yet, but it looks similar to your
> improvement of CTEs in a65724d.
No, that was about passing information the other way: from the
subquery's planning results out to the outer level. We would
need to do that, sure, but first we have to pass info down to
the subquery to say "results sorted like this could be useful".
As of v17 there is some mechanism to do that (see the setops
argument to subquery_planner), but I now realize that that
was designed in a really short-sighted fashion: it *only*
works with SetOperation nodes. We'd have to refactor that
so that what the upper query passes down is desired pathkeys,
or at least something closer to a pathkey than a SetOperation.
Another thing that's going on here is that the reason the
WHERE clause makes a difference is that it prevents flattening
the sub-query, per is_safe_append_member():
* Also, the child can't have any WHERE quals because there's no place to
* put them in an appendrel. (This is a bit annoying...)
I've never been entirely sure whether it is worth improving that.
Doing so would fix this particular issue, but there are plenty
of other un-flattenable sub-queries, so the other thing has a
potential for improving matters more widely.
regards, tom lane
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: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, lepihov@gmail.com, reshkekirill@gmail.com, pingw33n@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18751: Sub-optimal UNION ALL plan
In-Reply-To: <1531450.1735012018@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox