Re: Is select_outer_pathkeys_for_merge() too strict now we have Incremental Sorts?

[Richard Guo]

On Wed, Jul 20, 2022 at 11:03 AM David Rowley <dgrowle...@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