From: Andrei Lepikhov <lepihov@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: 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: Tue, 24 Dec 2024 09:57:31 +0700
Message-ID: <e405c80a-49ee-4a37-827d-d53b5189ad3e@gmail.com> (raw)
In-Reply-To: <1347038.1734967136@sss.pgh.pa.us>
References: <18751-887c7bf4e67e65a7@postgresql.org>
<CALdSSPg3Dvw1oy+AJgUX15hDgFHYkjt7w5gGfVABt1vbSNewUg@mail.gmail.com>
<1347038.1734967136@sss.pgh.pa.us>
On 12/23/24 22:18, Tom Lane wrote:
> Kirill Reshke <reshkekirill@gmail.com> writes:
>> I reproduced this on REL_16_STABLE, HEAD & REL_13_STABLE, so this is
>> not really a bug, just a missing optimization?
>
> 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.
BTW, SQL Server also uses bad variant of the plan for this query, or I
just don't know how to cook that soup properly.
--
regards, Andrei Lepikhov
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: lepihov@gmail.com, tgl@sss.pgh.pa.us, reshkekirill@gmail.com, pingw33n@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18751: Sub-optimal UNION ALL plan
In-Reply-To: <e405c80a-49ee-4a37-827d-d53b5189ad3e@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox