[
https://issues.apache.org/jira/browse/FLINK-40797?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Bonnie Varghese updated FLINK-40797:
------------------------------------
Summary: Exclude NestedLoopJoin from CBO search space when an equi-join
condition exists (was: Exclude NestedLoopJoin from CBO search space when an
equi-join condition exists and another equi-capable strategy is applicable)
> Exclude NestedLoopJoin from CBO search space when an equi-join condition
> exists
> -------------------------------------------------------------------------------
>
> 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
> Priority: Major
>
> `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)
