[jira] [Updated] (FLINK-40797) Exclude NestedLoopJoin from CBO search space when an equi-join condition exists

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

     [ 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)