Take account the number of fields in the index when calculating the cost

XMLWordPrintableJSON

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

      If there are multiple compound indexes that can be brought to bear to answer a query, they should have different costs so that the shortest index is chosen. Currently, they will all have the same cost so a cost-based decision can not properly be made. 

       

      
coll = db.foo;
coll.drop();for (let i = 0; i < 1000; i++) {
     coll.insertMany([{a: i, b: i, c: i}]);
}
coll.createIndex({a: 1});
coll.createIndex({a: 1, b:1});
coll.createIndex({a: 1, b:1,c:1});
db.adminCommand({setParameter: 1, planRankerMode: "multiPlanning"});
db.runCommand({analyze: "foo", key: "a"});
db.runCommand({analyze: "foo", key: "b"});
db.runCommand({analyze: "foo", key: "c"});
db.adminCommand({setParameter: 1, planRankerMode: "multiPlanning"});
db.foo.aggregate([{$match:{a: 1}}]).explain().queryPlanner;
db.adminCommand({setParameter: 1, planRankerMode: "automaticCE"});
db.foo.aggregate([{$match:{a: 1}}]).explain().queryPlanner;
db.adminCommand({setParameter: 1, planRankerMode: "histogramCE"});
db.foo.aggregate([{$match:{a: 1}}]).explain().queryPlanner;
db.adminCommand({setParameter: 1, planRankerMode: "heuristicCE"});
db.foo.aggregate([{$match:{a: 1}}]).explain().queryPlanner;

      This results in all the 3 possible plans having the same cost:

       

       winningPlan: {
    isCached: false,
    stage: 'FETCH',
    cardinalityEstimate: 31.622776601683793,
    costEstimate: 0.07146025286575786,
    estimatesMetadata: { ceSource: 'Heuristics' },
    inputStage: {
      stage: 'IXSCAN',
      cardinalityEstimate: 31.622776601683793,
      costEstimate: 0.026821114914099747,
      estimatesMetadata: { ceSource: 'Heuristics' },
      keyPattern: { a: 1, b: 1, c: 1 },
      indexName: 'a_1_b_1_c_1',
      isMultiKey: false,
      multiKeyPaths: { a: [], b: [], c: [] },
      isUnique: false,
      isSparse: false,
      isPartial: false,
      indexVersion: 2,
      direction: 'forward',
      indexBounds: {
        a: [ '[1, 1]' ],
        b: [ '[MinKey, MaxKey]' ],
        c: [ '[MinKey, MaxKey]' ]
      }
    }
  },
  rejectedPlans: [
    {
      isCached: false,
      stage: 'FETCH',
      cardinalityEstimate: 31.622776601683793,
      costEstimate: 0.07146025286575786,
      estimatesMetadata: { ceSource: 'Heuristics' },
      inputStage: {
        stage: 'IXSCAN',
        cardinalityEstimate: 31.622776601683793,
        costEstimate: 0.026821114914099747,
        estimatesMetadata: { ceSource: 'Heuristics' },
        keyPattern: { a: 1 },
        indexName: 'a_1',
        isMultiKey: false,
        multiKeyPaths: { a: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: { a: [ '[1, 1]' ] }
      }
    },
    {
      isCached: false,
      stage: 'FETCH',
      cardinalityEstimate: 31.622776601683793,
      costEstimate: 0.07146025286575786,
      estimatesMetadata: { ceSource: 'Heuristics' },
      inputStage: {
        stage: 'IXSCAN',
        cardinalityEstimate: 31.622776601683793,
        costEstimate: 0.026821114914099747,
        estimatesMetadata: { ceSource: 'Heuristics' },
        keyPattern: { a: 1, b: 1 },
        indexName: 'a_1_b_1',
        isMultiKey: false,
        multiKeyPaths: { a: [], b: [] },
        isUnique: false,
        isSparse: false,
        isPartial: false,
        indexVersion: 2,
        direction: 'forward',
        indexBounds: { a: [ '[1, 1]' ], b: [ '[MinKey, MaxKey]' ] }
      }
    }
  ]
}

            Assignee:
            Unassigned
            Reporter:
            Philip Stoev
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated: