-
Type:
Bug
-
Resolution: Duplicate
-
Priority:
Major - P3
-
None
-
Affects Version/s: 2.6.3
-
Component/s: Querying
-
Labels:
-
Query Optimization
-
ALL
-
Query 2019-04-08, Query 2019-07-29, Query 2019-08-12, Query 2019-08-26, Query 2019-09-09
-
(copied to CRM)
-
0
The nscanned value is the same for both plans, but we choose the one with a much higher nscannedObjects:
t.drop();
t.ensureIndex({type: 1});
t.ensureIndex({type: 1, folder: 1});
for (var i = 0; i < 110; i++) { t.insert({type: "folder", folder: "b"}); }
t.insert({type: "folder", folder: "m"});
t.find({type: "folder", folder: /m/}).explain(true)
I get the following explain output from a 2.6.3 server:
{
"cursor" : "BtreeCursor type_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 111,
"nscanned" : 111,
"nscannedObjectsAllPlans" : 112,
"nscannedAllPlans" : 222,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 1,
"nChunkSkips" : 0,
"millis" : 1,
"indexBounds" : {
"type" : [
[
"folder",
"folder"
]
]
},
"allPlans" : [
{
"cursor" : "BtreeCursor type_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 111,
"nscanned" : 111,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"type" : [
[
"folder",
"folder"
]
]
}
},
{
"cursor" : "BtreeCursor type_1_folder_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 111,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"type" : [
[
"folder",
"folder"
]
],
"folder" : [
[
"",
{
}
],
[
/m/,
/m/
]
]
}
}
],
"server" : "Macintosh.local:27017",
"filterSet" : false,
"stats" : {
"type" : "KEEP_MUTATIONS",
"works" : 113,
"yields" : 1,
"unyields" : 1,
"invalidates" : 0,
"advanced" : 1,
"needTime" : 110,
"needFetch" : 0,
"isEOF" : 1,
"children" : [
{
"type" : "FETCH",
"works" : 112,
"yields" : 1,
"unyields" : 1,
"invalidates" : 0,
"advanced" : 1,
"needTime" : 110,
"needFetch" : 0,
"isEOF" : 1,
"alreadyHasObj" : 0,
"forcedFetches" : 0,
"matchTested" : 1,
"children" : [
{
"type" : "IXSCAN",
"works" : 111,
"yields" : 1,
"unyields" : 1,
"invalidates" : 0,
"advanced" : 111,
"needTime" : 0,
"needFetch" : 0,
"isEOF" : 1,
"keyPattern" : "{ type: 1.0 }",
"boundsVerbose" : "field #0['type']: [\"folder\", \"folder\"]",
"isMultiKey" : 0,
"yieldMovedCursor" : 0,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0,
"matchTested" : 0,
"keysExamined" : 111,
"children" : [ ]
}
]
}
]
}
}
This happens because the plans tie: both return the same number of documents in the same amount of work cycles, even though one plan has to fetch more documents.
- is duplicated by
-
-
- Closed
-
-
SERVER-79400 Implement number of documents tie breaking heuristics
-
- Closed
-
- is related to
-
-
- Closed
-
-
-
- Closed
-
-
SERVER-37194 Number of docsExamined is not considered for index selection
-
- Closed
-
- links to