Details
-
Improvement
-
Resolution: Unresolved
-
Major - P3
-
None
-
None
-
None
-
Query Optimization
Description
Right now I don't think we are getting any advantage from using the index for $exists:true queries since we are always going to the row store. In fact, it is significantly slower because we are doing a bunch of point queries into the row store rather than using a (relatively) efficient collection scan. However, for covering indexes, we only need to go to the row store when the index says null to disambiguate missing fields from explicit null storage. For non-covering indexes we should probably just always use a collection scan because we need to go to the row store regardless of whether there is a match or not:
> db.products.explain('executionStats').find({type: {$exists:true}}, {_id:0, type: 1})
|
{
|
"explainVersion" : "1",
|
"queryPlanner" : {
|
"namespace" : "bestbuy.products",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"type" : {
|
"$exists" : true
|
}
|
},
|
"maxIndexedOrSolutionsReached" : false,
|
"maxIndexedAndSolutionsReached" : false,
|
"maxScansToExplodeReached" : false,
|
"winningPlan" : {
|
"stage" : "PROJECTION_SIMPLE",
|
"transformBy" : {
|
"_id" : 0,
|
"type" : 1
|
},
|
"inputStage" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"type" : {
|
"$exists" : true
|
}
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"type" : 1,
|
"customerReviewCount" : -1
|
},
|
"indexName" : "type_1_customerReviewCount_-1",
|
"isMultiKey" : false,
|
"multiKeyPaths" : {
|
"type" : [ ],
|
"customerReviewCount" : [ ]
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 2,
|
"direction" : "forward",
|
"indexBounds" : {
|
"type" : [
|
"[MinKey, MaxKey]"
|
],
|
"customerReviewCount" : [
|
"[MaxKey, MinKey]"
|
]
|
}
|
}
|
}
|
},
|
"rejectedPlans" : [
|
{
|
"stage" : "PROJECTION_SIMPLE",
|
"transformBy" : {
|
"_id" : 0,
|
"type" : 1
|
},
|
"inputStage" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"type" : {
|
"$exists" : true
|
}
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"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" : [
|
"[MinKey, MaxKey]"
|
],
|
"name" : [
|
"[MinKey, MaxKey]"
|
]
|
}
|
}
|
}
|
},
|
{
|
"stage" : "PROJECTION_SIMPLE",
|
"transformBy" : {
|
"_id" : 0,
|
"type" : 1
|
},
|
"inputStage" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"type" : {
|
"$exists" : true
|
}
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"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" : [
|
"[MinKey, MaxKey]"
|
],
|
"subclass" : [
|
"[MinKey, MaxKey]"
|
],
|
"genre" : [
|
"[MinKey, MaxKey]"
|
],
|
"format" : [
|
"[MinKey, MaxKey]"
|
]
|
}
|
}
|
}
|
},
|
{
|
"stage" : "PROJECTION_SIMPLE",
|
"transformBy" : {
|
"_id" : 0,
|
"type" : 1
|
},
|
"inputStage" : {
|
"stage" : "FETCH",
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"$_path" : 1,
|
"type" : 1
|
},
|
"indexName" : "$**_1",
|
"isMultiKey" : false,
|
"multiKeyPaths" : {
|
"$_path" : [ ],
|
"type" : [ ]
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 2,
|
"direction" : "forward",
|
"indexBounds" : {
|
"$_path" : [
|
"[\"type\", \"type\"]",
|
"[\"type.\", \"type/\")"
|
],
|
"type" : [
|
"[MinKey, MaxKey]"
|
]
|
}
|
}
|
}
|
}
|
]
|
},
|
"executionStats" : {
|
"executionSuccess" : true,
|
"nReturned" : 1901574,
|
"executionTimeMillis" : 3035,
|
"totalKeysExamined" : 1901574,
|
"totalDocsExamined" : 1901574,
|
"executionStages" : {
|
"stage" : "PROJECTION_SIMPLE",
|
"nReturned" : 1901574,
|
"executionTimeMillisEstimate" : 460,
|
"works" : 1901575,
|
"advanced" : 1901574,
|
"needTime" : 0,
|
"needYield" : 0,
|
"saveState" : 1901,
|
"restoreState" : 1901,
|
"isEOF" : 1,
|
"transformBy" : {
|
"_id" : 0,
|
"type" : 1
|
},
|
"inputStage" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"type" : {
|
"$exists" : true
|
}
|
},
|
"nReturned" : 1901574,
|
"executionTimeMillisEstimate" : 403,
|
"works" : 1901575,
|
"advanced" : 1901574,
|
"needTime" : 0,
|
"needYield" : 0,
|
"saveState" : 1901,
|
"restoreState" : 1901,
|
"isEOF" : 1,
|
"docsExamined" : 1901574,
|
"alreadyHasObj" : 0,
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"nReturned" : 1901574,
|
"executionTimeMillisEstimate" : 141,
|
"works" : 1901575,
|
"advanced" : 1901574,
|
"needTime" : 0,
|
"needYield" : 0,
|
"saveState" : 1901,
|
"restoreState" : 1901,
|
"isEOF" : 1,
|
"keyPattern" : {
|
"type" : 1,
|
"customerReviewCount" : -1
|
},
|
"indexName" : "type_1_customerReviewCount_-1",
|
"isMultiKey" : false,
|
"multiKeyPaths" : {
|
"type" : [ ],
|
"customerReviewCount" : [ ]
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 2,
|
"direction" : "forward",
|
"indexBounds" : {
|
"type" : [
|
"[MinKey, MaxKey]"
|
],
|
"customerReviewCount" : [
|
"[MaxKey, MinKey]"
|
]
|
},
|
"keysExamined" : 1901574,
|
"seeks" : 1,
|
"dupsTested" : 0,
|
"dupsDropped" : 0
|
}
|
}
|
}
|
},
|
"command" : {
|
"find" : "products",
|
"filter" : {
|
"type" : {
|
"$exists" : true
|
}
|
},
|
"projection" : {
|
"_id" : 0,
|
"type" : 1
|
},
|
"$db" : "bestbuy"
|
},
|
"serverInfo" : {
|
"host" : "ip-10-122-10-16",
|
"port" : 27017,
|
"version" : "4.9.0-alpha4-13-gbed3256",
|
"gitVersion" : "bed32560b4ef8df1eb6635c6d756119ab0e685a4"
|
},
|
"ok" : 1
|
}
|
Compare that to forcing a table scan:
> db.products.explain('executionStats').find({type: {$exists:true}}, {_id:0, type: 1}).hint({$natural:1})
|
{
|
"explainVersion" : "1",
|
"queryPlanner" : {
|
"namespace" : "bestbuy.products",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"type" : {
|
"$exists" : true
|
}
|
},
|
"maxIndexedOrSolutionsReached" : false,
|
"maxIndexedAndSolutionsReached" : false,
|
"maxScansToExplodeReached" : false,
|
"winningPlan" : {
|
"stage" : "PROJECTION_SIMPLE",
|
"transformBy" : {
|
"_id" : 0,
|
"type" : 1
|
},
|
"inputStage" : {
|
"stage" : "COLLSCAN",
|
"filter" : {
|
"type" : {
|
"$exists" : true
|
}
|
},
|
"direction" : "forward"
|
}
|
},
|
"rejectedPlans" : [ ]
|
},
|
"executionStats" : {
|
"executionSuccess" : true,
|
"nReturned" : 1901574,
|
"executionTimeMillis" : 1454,
|
"totalKeysExamined" : 0,
|
"totalDocsExamined" : 1901574,
|
"executionStages" : {
|
"stage" : "PROJECTION_SIMPLE",
|
"nReturned" : 1901574,
|
"executionTimeMillisEstimate" : 135,
|
"works" : 1901576,
|
"advanced" : 1901574,
|
"needTime" : 1,
|
"needYield" : 0,
|
"saveState" : 1901,
|
"restoreState" : 1901,
|
"isEOF" : 1,
|
"transformBy" : {
|
"_id" : 0,
|
"type" : 1
|
},
|
"inputStage" : {
|
"stage" : "COLLSCAN",
|
"filter" : {
|
"type" : {
|
"$exists" : true
|
}
|
},
|
"nReturned" : 1901574,
|
"executionTimeMillisEstimate" : 101,
|
"works" : 1901576,
|
"advanced" : 1901574,
|
"needTime" : 1,
|
"needYield" : 0,
|
"saveState" : 1901,
|
"restoreState" : 1901,
|
"isEOF" : 1,
|
"direction" : "forward",
|
"docsExamined" : 1901574
|
}
|
}
|
},
|
"command" : {
|
"find" : "products",
|
"filter" : {
|
"type" : {
|
"$exists" : true
|
}
|
},
|
"projection" : {
|
"_id" : 0,
|
"type" : 1
|
},
|
"hint" : {
|
"$natural" : 1
|
},
|
"$db" : "bestbuy"
|
},
|
"serverInfo" : {
|
"host" : "ip-10-122-10-16",
|
"port" : 27017,
|
"version" : "4.9.0-alpha4-13-gbed3256",
|
"gitVersion" : "bed32560b4ef8df1eb6635c6d756119ab0e685a4"
|
},
|
"ok" : 1
|
}
|
It takes 1.5s whereas using the index+fetch is 3s.
Attachments
Issue Links
- is related to
-
SERVER-67378 Index null values and missing values differently
-
- Closed
-
-
SERVER-12869 Index null values and missing values differently
-
- Backlog
-