|
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.
|