Details
-
Bug
-
Status: Closed
-
Major - P3
-
Resolution: Duplicate
-
2.6.3
-
None
-
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
Description
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.
Attachments
Issue Links
- is duplicated by
-
SERVER-57988 Unfortunate index selection leads to too many index/document scans
-
- Closed
-
-
SERVER-79400 Implement number of documents tie breaking heuristics
-
- Closed
-
- is related to
-
SERVER-13675 Plans with differing performance can tie during plan ranking
-
- Closed
-
-
SERVER-14311 skipping of index keys is not accounted for in plan ranking by the index scan stage
-
- Closed
-
-
SERVER-37194 Number of docsExamined is not considered for index selection
-
- Closed
-
- links to