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 CAMbWs49ST81+94V6j72r0jp0kGaRMuTauNyfjW4GDoNzRDFhXw@mail.gmail.com
Whole thread Raw
In response to Re: 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 Thu, Jul 21, 2022 at 3:47 AM David Rowley <dgrowleyml@gmail.com> wrote:
On Wed, 20 Jul 2022 at 21:19, Richard Guo <guofenglinux@gmail.com> wrote:
> 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:

I don't think that's a clear-cut win. There is scoring code in there
to try to arrange the pathkey list in the order of
most-useful-to-upper-level-joins firsts.  If we were to do as you
describe we could end up generating worse plans when there is some
subsequent Merge Join above this one that has join conditions that the
query_pathkeys are not compatible with.

Yeah, you're right. Although we would try different permutation of the
pathkeys in sort_inner_and_outer() but that does not cover every
possible ordering due to cost consideration. So we still need to respect
the heuristics behind the pathkey order returned by this function, which
is the scoring logic trying to list most-useful-to-upper-level-joins
keys earlier.
 
Maybe your idea could be made to work in cases where
bms_equal(joinrel->relids, root->all_baserels). In that case, we
should not be processing any further joins and don't need to consider
that as a factor for the scoring.

That should work, as long as this case is common enough to worth we
writing the codes.

Thanks
Richard

pgsql-hackers by date:

Previous
From: Fujii Masao
Date:
Subject: Re: Improve description of XLOG_RUNNING_XACTS
Next
From: Gurjeet Singh
Date:
Subject: Fwd: Unprivileged user can induce crash by using an SUSET param in PGOPTIONS