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

On Wed, Jul 20, 2022 at 11:03 AM David Rowley <dgrowleyml(at)gmail(dot)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