-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
Query Optimization
Compare explain for these two queries:
db.foo.explain().find({$expr:{$and:[ {$eq:["$_id",15]} ]}})
{
"explainVersion" : "1",
"queryPlanner" : {
"namespace" : "test.foo",
"parsedQuery" : {
"$and" : [
{
"$expr" : {
"$and" : [
{
"$eq" : [
"$_id",
{
"$const" : 15
}
]
}
]
}
},
{
"_id" : {
"$_internalExprEq" : 15
}
}
]
},
"indexFilterSet" : false,
"queryHash" : "300502E0",
"planCacheKey" : "53380807",
"optimizationTimeMillis" : 0,
"maxIndexedOrSolutionsReached" : false,
"maxIndexedAndSolutionsReached" : false,
"maxScansToExplodeReached" : false,
"prunedSimilarIndexes" : false,
"winningPlan" : {
"isCached" : false,
"stage" : "FETCH",
"filter" : {
"$expr" : {
"$and" : [
{
"$eq" : [
"$_id",
{
"$const" : 15
}
]
}
]
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"_id" : 1
},
// etc
and same with a constant/true expression inside $and
db.foo.explain().find({$expr:{$and:[ {$eq:["$_id",15]}, true ]}})
{
"explainVersion" : "1",
"queryPlanner" : {
"namespace" : "test.foo",
"parsedQuery" : {
"$expr" : {
"$and" : [
{
"$eq" : [
"$_id",
{
"$const" : 15
}
]
}
]
}
},
"indexFilterSet" : false,
"queryHash" : "844E5AF4",
"planCacheKey" : "FC8990BC",
"optimizationTimeMillis" : 0,
"maxIndexedOrSolutionsReached" : false,
"maxIndexedAndSolutionsReached" : false,
"maxScansToExplodeReached" : false,
"prunedSimilarIndexes" : false,
"winningPlan" : {
"isCached" : false,
"stage" : "COLLSCAN",
"filter" : {
"$expr" : {
"$and" : [
{
"$eq" : [
"$_id",
{
"$const" : 15
}
]
}
]
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
}, //etc
I don't see
{
"_id" : {
"$_internalExprEq" : 15
}
}
so the _id index is not being used...