|
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.
|