Re: Is select_outer_pathkeys_for_merge() too strict now we have Incremental Sorts? - Mailing list pgsql-hackers

From Richard Guo
Subject Re: Is select_outer_pathkeys_for_merge() too strict now we have Incremental Sorts?
Date
Msg-id CAMbWs48tT8_pXxGwjiFS-Ot65m0MCmDYnNaQ2kOHM-FrnLTgHA@mail.gmail.com
Whole thread Raw
In response to Is select_outer_pathkeys_for_merge() too strict now we have Incremental Sorts?  (David Rowley <dgrowleyml@gmail.com>)
Responses Re: Is select_outer_pathkeys_for_merge() too strict now we have Incremental Sorts?
List pgsql-hackers

On Wed, Jul 20, 2022 at 11:03 AM David Rowley <dgrowleyml@gmail.com> wrote:
I think we can relax this now that we have incremental sort.  I think
a better way to limit this is to allow a prefix of the query_pathkeys
providing that covers *all* of the join pathkeys.  That way, for the
above query, it leaves it open for the planner to do the Merge Join by
sorting by a.a DESC then just do an Incremental Sort to get the
GroupAggregate input sorted by a.b.

So the idea is if the ECs used by the mergeclauses are prefix of the
query_pathkeys, we use this prefix as pathkeys for the mergejoin. Why
not relax this further that if the ECs in the mergeclauses and the
query_pathkeys have common prefix, we use that prefix as pathkeys? So
that we can have a plan like below:

# explain (costs off) select * from t1 join t2 on t1.c = t2.c and t1.a = t2.a order by t1.a DESC, t1.b;
                      QUERY PLAN
-------------------------------------------------------
 Incremental Sort
   Sort Key: t1.a DESC, t1.b
   Presorted Key: t1.a
   ->  Merge Join
         Merge Cond: ((t1.a = t2.a) AND (t1.c = t2.c))
         ->  Sort
               Sort Key: t1.a DESC, t1.c
               ->  Seq Scan on t1
         ->  Sort
               Sort Key: t2.a DESC, t2.c
               ->  Seq Scan on t2
(11 rows)

Thanks
Richard

pgsql-hackers by date:

Previous
From: Nikita Malakhov
Date:
Subject: Re: Pluggable toaster
Next
From: Juan José Santamaría Flecha
Date:
Subject: Re: Windows default locale vs initdb