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

      We currently pass to MP all zeroCE plans and the winning estimable plan, even if there is a single zeroCE plan.

      We can instead treat the zeroCE plan as the unambiguous CBR winner, if there is a single one.

      Repro:

      db.adminCommand({
          setParameter:1,
          internalQueryPlanRanker: "mixed",
          internalQueryCBRCEMode: "samplingCE",
          internalQueryMixedPlanRankingStrategy: "NoMultiplanningResults",
          internalQuerySamplingCEMethod: "random",
          internalQueryPlanEvaluationWorks: 100,
          internalSamplingSizeOverride: 1
      });
      db.b.drop();
      db.b.createIndex({a: 1});
      db.b.createIndex({b: 1});
      db.b.insertMany(Array.from({length: 2000}, () => ({a: 0})));
      db.b.insertMany(Array.from({length: 200}, () => ({b: 0})));
      db.b.explain().find({a: 0, b: 0});
      

      Returns (with ~90% probability):

          winningPlan: {
            isCached: false,
            stage: 'FETCH',
            filter: { a: { '$eq': 0 } },
            nss: 'test.b',
            inputStage: {
              stage: 'IXSCAN',
              nss: 'test.b',
              keyPattern: { b: 1 },
              indexName: 'b_1',
              isMultiKey: false,
              multiKeyPaths: { b: [] },
              isUnique: false,
              isSparse: false,
              isPartial: false,
              indexVersion: 2,
              direction: 'forward',
              indexBounds: { b: [ '[0, 0]' ] }
            }
          },
          rejectedPlans: [
            {
              isCached: false,
              stage: 'FETCH',
              filter: { b: { '$eq': 0 } },
              nss: 'test.b',
              inputStage: {
                stage: 'IXSCAN',
                nss: 'test.b',
                keyPattern: { a: 1 },
                indexName: 'a_1',
                isMultiKey: false,
                multiKeyPaths: { a: [] },
                isUnique: false,
                isSparse: false,
                isPartial: false,
                indexVersion: 2,
                direction: 'forward',
                indexBounds: { a: [ '[0, 0]' ] }
              }
            },
            {
              isCached: false,
              stage: 'FETCH',
              costEstimate: 4.6280207099999995,
              cardinalityEstimate: 1,
              estimatesMetadata: { ceSource: 'Sampling' },
              filter: { b: { '$eq': 0 } },
              nss: 'test.b',
              inputStage: {
                stage: 'IXSCAN',
                costEstimate: 0.95934471,
                cardinalityEstimate: 2200,
                estimatesMetadata: { ceSource: 'Sampling' },
                numKeysEstimate: 2200,
                indexSeekEstimate: 1,
                nss: 'test.b',
                keyPattern: { a: 1 },
                indexName: 'a_1',
                isMultiKey: false,
                multiKeyPaths: { a: [] },
                isUnique: false,
                isSparse: false,
                isPartial: false,
                indexVersion: 2,
                direction: 'forward',
                indexBounds: { a: [ '[0, 0]' ] }
              }
            },
            {
              isCached: false,
              stage: 'FETCH',
              filter: { a: { '$eq': 0 } },
              nss: 'test.b',
              inputStage: {
                stage: 'IXSCAN',
                costEstimate: 1.7976931348623157e+308,
                cardinalityEstimate: 0,
                estimatesMetadata: { ceSource: 'Sampling' },
                numKeysEstimate: 0,
                indexSeekEstimate: 1,
                nss: 'test.b',
                keyPattern: { b: 1 },
                indexName: 'b_1',
                isMultiKey: false,
                multiKeyPaths: { b: [] },
                isUnique: false,
                isSparse: false,
                isPartial: false,
                indexVersion: 2,
                direction: 'forward',
                indexBounds: { b: [ '[0, 0]' ] }
              }
            }
          ]
      

      ie. MP decides the winner even though only one plan is zeroCE (has numKeysEstimate == 0).

            Assignee:
            Unassigned
            Reporter:
            Kartal Kaan Bozdogan
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Created:
              Updated: