Re: BUG #18751: Sub-optimal UNION ALL plan

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