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

["Sergey Nuyanzin (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/FLINK-40797?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18125603#comment-18125603 ] 

Sergey Nuyanzin commented on FLINK-40797:
-----------------------------------------

Merged as [60846c0cdbf57d5297c44f218025b189d18a2765|https://github.com/apache/flink/commit/60846c0cdbf57d5297c44f218025b189d18a2765]

> 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
>              Labels: pull-request-available
>
> `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)