From: Andrei Lepikhov <lepihov@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
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: Tue, 24 Dec 2024 16:05:38 +0700
Message-ID: <bcbe800f-56b8-498a-ace3-94ecbe1a90ad@gmail.com> (raw)
In-Reply-To: <1531450.1735012018@sss.pgh.pa.us>
References: <18751-887c7bf4e67e65a7@postgresql.org>
<CALdSSPg3Dvw1oy+AJgUX15hDgFHYkjt7w5gGfVABt1vbSNewUg@mail.gmail.com>
<1347038.1734967136@sss.pgh.pa.us>
<e405c80a-49ee-4a37-827d-d53b5189ad3e@gmail.com>
<1531450.1735012018@sss.pgh.pa.us>
On 12/24/24 10:46, Tom Lane wrote:
> Andrei Lepikhov <lepihov@gmail.com> writes:
>> On 12/23/24 22:18, Tom Lane wrote:
> 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.
I have waited for such a proposal for years!
It would be beneficial, of course. I may imagine an implementation that
will consider LIMIT statements or grouping columns because they exist in
the parse tree.
But sometimes I have seen examples where MergeJoin or IncrementalSort
could bring speedup in case of sorted subquery output. But without
planning, we don't realise we need this type of sorting.
For example:
------------
CREATE TABLE test (x int PRIMARY KEY);
INSERT INTO test SELECT gs FROM generate_series(1,1E4) AS gs;
VACUUM ANALYZE;
SET enable_nestloop = f;
SET enable_hashjoin = f;
EXPLAIN (COSTS OFF)
SELECT q1.x FROM
(SELECT t1.x FROM test t1 LIMIT 10) AS q1, test t2
WHERE q1.x=t2.x LIMIT 10;
/*
Limit
-> Merge Join
Merge Cond: (t2.x = t1.x)
-> Index Only Scan using test_pkey on test t2
-> Sort
Sort Key: t1.x
-> Limit
-> Seq Scan on test t1
*/
--
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: <bcbe800f-56b8-498a-ace3-94ecbe1a90ad@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