Details
-
Improvement
-
Resolution: Unresolved
-
Major - P3
-
None
-
None
-
None
-
None
-
Query Optimization
Description
In the bestbuy high-value workload distinct_types_with_in_predicate-useAgg, we run an aggregation
db.products.aggregate([{$match: {type: {$in: ["Game", "Movie", "Music", "HardGood"]}}}, {$group: {_id: "$type"}}]) |
which results in a plan
stages: [
|
{
|
'$cursor': { |
queryPlanner: {
|
namespace: 'bestbuy.products', |
indexFilterSet: false, |
parsedQuery: { type: { '$in': [ 'Game', 'HardGood', 'Movie', 'Music' ] } }, |
queryHash: '24FB8A6A', |
planCacheKey: '6DBEACC2', |
optimizationTimeMillis: 0, |
maxIndexedOrSolutionsReached: false, |
maxIndexedAndSolutionsReached: false, |
maxScansToExplodeReached: false, |
winningPlan: {
|
isCached: false, |
stage: 'PROJECTION_COVERED', |
transformBy: { type: 1, _id: 0 }, |
inputStage: {
|
stage: 'DISTINCT_SCAN', |
keyPattern: { type: 1, subclass: 1, genre: 1, format: 1 }, |
indexName: 'type_1_subclass_1_genre_1_format_1', |
isMultiKey: false, |
multiKeyPaths: { type: [], subclass: [], genre: [], format: [] },
|
isUnique: false, |
isSparse: false, |
isPartial: false, |
indexVersion: 2, |
direction: 'forward', |
indexBounds: {
|
type: [
|
'["Game", "Game"]', |
'["HardGood", "HardGood"]', |
'["Movie", "Movie"]', |
'["Music", "Music"]' |
],
|
subclass: [ '[MinKey, MaxKey]' ], |
genre: [ '[MinKey, MaxKey]' ], |
format: [ '[MinKey, MaxKey]' ] |
}
|
}
|
},
|
rejectedPlans: []
|
}
|
}
|
},
|
{ '$groupByDistinctScan': { newRoot: { _id: '$type' } } } |
],
|
However, there is a shorter index which can satisfy the query that the planner does not consider,
db.products.aggregate([{$match: {type: {$in: ["Game", "Movie", "Music", "HardGood"]}}}, {$group: {_id: "$type"}}], {hint: {type: 1, name: 1}}) |
stages: [
|
{
|
'$cursor': { |
queryPlanner: {
|
namespace: 'bestbuy.products', |
indexFilterSet: false, |
parsedQuery: { type: { '$in': [ 'Game', 'HardGood', 'Movie', 'Music' ] } }, |
queryHash: '24FB8A6A', |
planCacheKey: '6DBEACC2', |
optimizationTimeMillis: 0, |
maxIndexedOrSolutionsReached: false, |
maxIndexedAndSolutionsReached: false, |
maxScansToExplodeReached: false, |
winningPlan: {
|
isCached: false, |
stage: 'PROJECTION_COVERED', |
transformBy: { type: 1, _id: 0 }, |
inputStage: {
|
stage: 'DISTINCT_SCAN', |
keyPattern: { type: 1, name: 1 }, |
indexName: 'type_1_name_1', |
isMultiKey: false, |
multiKeyPaths: { type: [], name: [] },
|
isUnique: false, |
isSparse: false, |
isPartial: false, |
indexVersion: 2, |
direction: 'forward', |
indexBounds: {
|
type: [
|
'["Game", "Game"]', |
'["HardGood", "HardGood"]', |
'["Movie", "Movie"]', |
'["Music", "Music"]' |
],
|
name: [ '[MinKey, MaxKey]' ] |
}
|
}
|
},
|
rejectedPlans: []
|
}
|
}
|
},
|
{ '$groupByDistinctScan': { newRoot: { _id: '$type' } } } |
],
|
We should use the shorter index in this case.