[jira] [Created] (FLINK-40797) Exclude NestedLoopJoin from CBO search space when an equi-join condition exists and another equi-capable strategy is applicable

["Bonnie Varghese (Jira)" <ji...@apache.org>]

Bonnie Varghese created FLINK-40797:
---------------------------------------

             Summary: Exclude NestedLoopJoin from CBO search space when an equi-join condition exists and another equi-capable strategy is applicable
                 Key: FLINK-40797
                 URL: https://issues.apache.org/jira/browse/FLINK-40797
             Project: Flink
          Issue Type: Improvement
          Components: Table SQL / Planner
            Reporter: Bonnie Varghese
            Assignee: Bonnie Varghese


`BatchPhysicalJoinRuleBase.checkNestLoopJoin` currently lets nested-loop join (NLJ) compete on cost alone against `BroadcastHashJoin`, `ShuffleHashJoin`, and `SortMergeJoin`, even for plain equi-joins. When the cost model underestimates cardinality (i.e. due to partial stats e.g. missing NDV/column stats), NLJ's O(m·n) cost can look artificially cheaper than a hash-based join's O(m+n) cost, so the CBO picks NLJ for a join that has an equi-condition and could just as well use a hash-based strategy.

With row-count stats alone, TPC-DS Q47's self-join regressed onto a nested-loop join instead of a hash join under this condition, blowing up runtime.

Proposed fix:

In checkNestLoopJoin, once we already know the join is an equi-join (isEquivJoin) and at least one equi-capable strategy is applicable to it, remove NLJ from the candidate set entirely rather than relying on the cost comparison to sort it out. An explicit NEST_LOOP join hint still overrides this and forces NLJ.



Note: This narrows how often NLJ is selectable at plan time but does not address the runtime case where a job discovers mid-execution that its build side is far larger than the optimizer estimated (see FLINK-16165)



--
This message was sent by Atlassian Jira
(v8.20.10#820010)