-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
Query Optimization
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]' ] }
}
}
]
}
- is duplicated by
-
-
- Closed
-
- is related to
-
-
- Closed
-