-
Type:
Improvement
-
Resolution: Duplicate
-
Priority:
Minor - P4
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
Query Optimization
When selecting 2 fileds from an Index with a skip and limit there is a fetch added which is not needed as the index already fully covers the needed fields.
Repeatable test:
db.a.createIndex({a: 1, bin:1})
x=db.a.insertMany(Array.from({length:1000000}).map((_, idx) => {return {bin: (Math.random() < 0.5) ? 1 : 0, a:idx};}))
db.a.find({bin: {$in: [0, 1]}}).sort({a:1}).skip(500000).limit(1).hint('a_1_bin_1').explain()
The resulting plan is:
"winningPlan" : {
"isCached" : false,
"stage" : "LIMIT",
"limitAmount" : 1,
"inputStage" : {
"stage" : "SKIP",
"skipAmount" : 500000,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"bin" : {
"$in" : [
0,
1
]
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"a" : 1,
"bin" : 1
},
"indexName" : "a_1_bin_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"a" : [ ],
"bin" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"a" : [
"[MinKey, MaxKey]"
],
"bin" : [
"[MinKey, MaxKey]"
]
}
}
}
This plan uses a fetch to filter on the bin value instead of using the data returned by the index. This causes the plan to inspect 50k documents. Additionally to build the result set a fetch is not necessary as the index already contains the fields required.
- duplicates
-
-
- Open
-