-
Type: Improvement
-
Resolution: Unresolved
-
Priority: Major - P3
-
None
-
Affects Version/s: 7.1.0-rc0
-
Component/s: None
-
None
-
Query Optimization
The setup:
assert.commandWorked(coll.createIndex({_id: 1, a: 1}));
coll.insert({_id: 1, a: 1});
coll.insert({_id: 2, a: 2});
coll.insert({_id: 3, a: 2});
When running an explain for `coll.find({_id: 2, a: 2})` we get only one winning plan, that is using an _id field. The newly introduced index is not taken into account and is not part of the rejected plans.
{ "explainVersion" : "2", "queryPlanner" : { "namespace" : "test.query_settings_cmds", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "_id" : { "$eq" : 2 } }, { "a" : { "$eq" : 2 } } ] }, "queryHash" : "FA27A693", "planCacheKey" : "7E57B88A", "maxIndexedOrSolutionsReached" : false, "maxIndexedAndSolutionsReached" : false, "maxScansToExplodeReached" : false, "winningPlan" : { "queryPlan" : { "stage" : "FETCH", "planNodeId" : 2, "filter" : { "a" : { "$eq" : 2 } }, "inputStage" : { "stage" : "IXSCAN", "planNodeId" : 1, "keyPattern" : { "_id" : 1 }, "indexName" : "_id_", "isMultiKey" : false, "isUnique" : true, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "_id" : [ "[2.0, 2.0]" ] } } }, "slotBasedPlan" : { "slots" : "$$RESULT=s8 env: { s3 = KS(2B04FE04), s11 = 2, s2 = KS(2B040104), s7 = {\"_id\" : 1} }", "stages" : "[2] filter {traverseF(s10, lambda(l1.0) { ((l1.0 == s11) ?: false) }, false)} \n[2] nlj inner [] [s1, s4, s5, s6, s7] \n left \n [1] cfilter {(exists(s2) && exists(s3))} \n [1] ixseek s2 s3 s6 s1 s4 s5 [] @\"1ae72221-0cb8-4cf5-8162-53f0b93c8ced\" @\"_id_\" true \n right \n [2] limit 1 \n [2] seek s1 s8 s9 s4 s5 s6 s7 none none [s10 = a] @\"1ae72221-0cb8-4cf5-8162-53f0b93c8ced\" true false \n" } }, "rejectedPlans" : [ ] }, "command" : { "find" : "query_settings_cmds", "filter" : { "_id" : 2, "a" : 2 }, "$db" : "test" }, "serverInfo" : { "host" : "ip-10-122-5-229", "port" : 20000, "version" : "7.1.0-alpha", "gitVersion" : "unknown" }, "serverParameters" : { "internalQueryFacetBufferSizeBytes" : 104857600, "internalQueryFacetMaxOutputDocSizeBytes" : 104857600, "internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600, "internalDocumentSourceGroupMaxMemoryBytes" : 104857600, "internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600, "internalQueryProhibitBlockingMergeOnMongoS" : 0, "internalQueryMaxAddToSetBytes" : 104857600, "internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600, "internalQueryFrameworkControl" : "trySbeEngine" }, "ok" : 1 }
With the index hint, however, we can generate a plan with the given index (`coll.find({_id: 2, a: 2}).hint({_id: 1, a: 1})`):
{ "explainVersion" : "2", "queryPlanner" : { "namespace" : "test.query_settings_cmds", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "_id" : { "$eq" : 2 } }, { "a" : { "$eq" : 2 } } ] }, "queryHash" : "E67D31E6", "planCacheKey" : "E7B1B151", "maxIndexedOrSolutionsReached" : false, "maxIndexedAndSolutionsReached" : false, "maxScansToExplodeReached" : false, "winningPlan" : { "queryPlan" : { "stage" : "FETCH", "planNodeId" : 2, "inputStage" : { "stage" : "IXSCAN", "planNodeId" : 1, "keyPattern" : { "_id" : 1, "a" : 1 }, "indexName" : "_id_1_a_1", "isMultiKey" : false, "multiKeyPaths" : { "_id" : [ ], "a" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "_id" : [ "[2.0, 2.0]" ], "a" : [ "[2.0, 2.0]" ] } } }, "slotBasedPlan" : { "slots" : "$$RESULT=s8 env: { s3 = KS(2B042B04FE04), s7 = {\"_id\" : 1, \"a\" : 1}, s2 = KS(2B042B040104) }", "stages" : "[2] nlj inner [] [s1, s4, s5, s6, s7] \n left \n [1] cfilter {(exists(s2) && exists(s3))} \n [1] ixseek s2 s3 s6 s1 s4 s5 [] @\"1ae72221-0cb8-4cf5-8162-53f0b93c8ced\" @\"_id_1_a_1\" true \n right \n [2] limit 1 \n [2] seek s1 s8 s9 s4 s5 s6 s7 none none [] @\"1ae72221-0cb8-4cf5-8162-53f0b93c8ced\" true false \n" } }, "rejectedPlans" : [ ] }, "command" : { "find" : "query_settings_cmds", "filter" : { "_id" : 2, "a" : 2 }, "hint" : { "_id" : 1, "a" : 1 }, "$db" : "test" }, "serverInfo" : { "host" : "ip-10-122-5-229", "port" : 20000, "version" : "7.1.0-alpha", "gitVersion" : "unknown" }, "serverParameters" : { "internalQueryFacetBufferSizeBytes" : 104857600, "internalQueryFacetMaxOutputDocSizeBytes" : 104857600, "internalLookupStageIntermediateDocumentMaxSizeBytes" : 104857600, "internalDocumentSourceGroupMaxMemoryBytes" : 104857600, "internalQueryMaxBlockingSortMemoryUsageBytes" : 104857600, "internalQueryProhibitBlockingMergeOnMongoS" : 0, "internalQueryMaxAddToSetBytes" : 104857600, "internalDocumentSourceSetWindowFieldsMaxMemoryBytes" : 104857600, "internalQueryFrameworkControl" : "trySbeEngine" }, "ok" : 1 }
In theory the composite index should be preferred, as in case of a mismatch, no FETCH is needed to evaluate the predicate.
While the affected version is 7.1, the assumption is that this is and old bug present on other branches as well.