|
This just came up again in this example:
db.accounts.explain().find({ balance: { $gt: 1300 } }, { account_holder: 1, balance: 1, _id: 0}).sort({ account_holder: 1, balance: -1 })
|
{
|
explainVersion: '1',
|
queryPlanner: {
|
namespace: 'test.accounts',
|
indexFilterSet: false,
|
parsedQuery: { balance: { '$gt': 1300 } },
|
queryHash: '3833F71B',
|
planCacheKey: '6BA9E76C',
|
maxIndexedOrSolutionsReached: false,
|
maxIndexedAndSolutionsReached: false,
|
maxScansToExplodeReached: false,
|
winningPlan: {
|
stage: 'PROJECTION_SIMPLE',
|
transformBy: { account_holder: 1, balance: 1, _id: 0 },
|
inputStage: {
|
stage: 'FETCH',
|
filter: { balance: { '$gt': 1300 } },
|
inputStage: {
|
stage: 'IXSCAN',
|
keyPattern: { account_holder: 1, balance: -1 },
|
indexName: 'account_holder_1_balance_-1',
|
isMultiKey: false,
|
multiKeyPaths: { account_holder: [], balance: [] },
|
isUnique: false,
|
isSparse: false,
|
isPartial: false,
|
indexVersion: 2,
|
direction: 'forward',
|
indexBounds: {
|
account_holder: [ '[MinKey, MaxKey]' ],
|
balance: [ '[MaxKey, MinKey]' ]
|
}
|
}
|
}
|
},
|
|
We are already scanning the full index and can filter balance in the IXSCAN part, but we do a FETCH anyway.
|
|
Here is an example from related ticket that's manifestation of this issue:
Collection with index on a, b and query with equality on b and sort on a. We use the index for sort but still fetch to test "b".
db.foo.find({b: 0}, {a: 1, b: 1, _id: 0}).sort({a: 1}).explain(true)
|
{
|
"queryPlanner" : {
|
"plannerVersion" : 1,
|
"namespace" : "test.foo",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"b" : {
|
"$eq" : 0
|
}
|
},
|
"winningPlan" : {
|
"stage" : "PROJECTION",
|
"transformBy" : {
|
"a" : 1,
|
"b" : 1,
|
"_id" : 0
|
},
|
"inputStage" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"b" : {
|
"$eq" : 0
|
}
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"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" : [
|
"[MinKey, MaxKey]"
|
],
|
"b" : [
|
"[MinKey, MaxKey]"
|
]
|
}
|
}
|
}
|
},
|
"rejectedPlans" : [ ]
|
},
|
"executionStats" : {
|
"executionSuccess" : true,
|
"nReturned" : 0,
|
"executionTimeMillis" : 0,
|
"totalKeysExamined" : 0,
|
"totalDocsExamined" : 0,
|
"executionStages" : {
|
"stage" : "PROJECTION",
|
"nReturned" : 0,
|
"executionTimeMillisEstimate" : 0,
|
"works" : 1,
|
"advanced" : 0,
|
"needTime" : 0,
|
"needYield" : 0,
|
"saveState" : 0,
|
"restoreState" : 0,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"transformBy" : {
|
"a" : 1,
|
"b" : 1,
|
"_id" : 0
|
},
|
"inputStage" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"b" : {
|
"$eq" : 0
|
}
|
},
|
"nReturned" : 0,
|
"executionTimeMillisEstimate" : 0,
|
"works" : 1,
|
"advanced" : 0,
|
"needTime" : 0,
|
"needYield" : 0,
|
"saveState" : 0,
|
"restoreState" : 0,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"docsExamined" : 0,
|
"alreadyHasObj" : 0,
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"nReturned" : 0,
|
"executionTimeMillisEstimate" : 0,
|
"works" : 1,
|
"advanced" : 0,
|
"needTime" : 0,
|
"needYield" : 0,
|
"saveState" : 0,
|
"restoreState" : 0,
|
"isEOF" : 1,
|
"invalidates" : 0,
|
"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" : [
|
"[MinKey, MaxKey]"
|
],
|
"b" : [
|
"[MinKey, MaxKey]"
|
]
|
},
|
"keysExamined" : 0,
|
"seeks" : 1,
|
"dupsTested" : 0,
|
"dupsDropped" : 0,
|
"seenInvalidated" : 0
|
}
|
}
|
},
|
"allPlansExecution" : [ ]
|
},
|
"serverInfo" : {
|
"host" : "Asyas-MacBook-Pro.local",
|
"port" : 27017,
|
"version" : "4.0.0",
|
"gitVersion" : "3b07af3d4f471ae89e8186d33bbb1d5259597d51"
|
},
|
"ok" : 1
|
}
|
|