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)
