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

On 12/24/24 10:46, Tom Lane wrote:
> Andrei Lepikhov <lepihov(at)gmail(dot)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