[Join Optimization] Expose CE for single table predicates of RHS of INLJ in explain

    • Type: Improvement
    • Resolution: Unresolved
    • Priority: Major - P3
    • None
    • Affects Version/s: None
    • Component/s: None
    • None
    • Query Optimization
    • None
    • None
    • None
    • None
    • None
    • None
    • None

      As part of SERVER-111473, we decided to not annotate Index probes with any estimate information because it would be confusing. INLJ is different from from HJ and NLJ in that it applies the join predicates before single table predicates, so showing an estimate for single table CE on the probe node is misleading.

      However, during debugging it can be useful to know both the join CE of INLJ and the single table estimate of the RHS. This can help understand the selectivity of the join and single table predicates.

      An idea for this is to add an entry on the INLJ itself:

       

      stage: 'INDEXED_NESTED_LOOP_JOIN_EMBEDDING',
      costEstimate: 1072.36,
      cardinalityEstimate: 166358.44,
      cardinalityRHSBeforeJoinPred: 1234, <--- This entry is new.
      estimatesMetadata: { ceSource: 'Metadata' },
      leftEmbeddingField: 'none',
      rightEmbeddingField: 'lineitem',
      joinPredicates: [ 's_suppkey = l_suppkey' ],
      inputStages: [ ... ]

       

            Assignee:
            Unassigned
            Reporter:
            Ben Shteinfeld
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated: