Re: Is select_outer_pathkeys_for_merge() too strict now we have Incremental Sorts?
От | Richard Guo |
---|---|
Тема | Re: Is select_outer_pathkeys_for_merge() too strict now we have Incremental Sorts? |
Дата | |
Msg-id | CAMbWs48tT8_pXxGwjiFS-Ot65m0MCmDYnNaQ2kOHM-FrnLTgHA@mail.gmail.com обсуждение исходный текст |
Ответ на | Is select_outer_pathkeys_for_merge() too strict now we have Incremental Sorts? (David Rowley <dgrowleyml@gmail.com>) |
Ответы |
Re: Is select_outer_pathkeys_for_merge() too strict now we have Incremental Sorts?
|
Список | 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
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 по дате отправления: