Message-ID: <bcbe800f-56b8-498a-ace3-94ecbe1a90ad@gmail.com>
Date: Tue, 24 Dec 2024 16:05:38 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18751: Sub-optimal UNION ALL plan
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kirill Reshke <reshkekirill@gmail.com>, pingw33n@gmail.com,
 pgsql-bugs@lists.postgresql.org
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>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <1531450.1735012018@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/bcbe800f-56b8-498a-ace3-94ecbe1a90ad%40gmail.com>
Precedence: bulk

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