From: 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: Mon, 23 Dec 2024 10:18:56 -0500
Message-ID: <1347038.1734967136@sss.pgh.pa.us> (raw)
In-Reply-To: <CALdSSPg3Dvw1oy+AJgUX15hDgFHYkjt7w5gGfVABt1vbSNewUg@mail.gmail.com>
References: <18751-887c7bf4e67e65a7@postgresql.org>
<CALdSSPg3Dvw1oy+AJgUX15hDgFHYkjt7w5gGfVABt1vbSNewUg@mail.gmail.com>
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.
You could work around this by writing the second sub-SELECT like
(select * from t2 where ... order by t)
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, reshkekirill@gmail.com, pingw33n@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18751: Sub-optimal UNION ALL plan
In-Reply-To: <1347038.1734967136@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