-
Type:
Improvement
-
Resolution: Duplicate
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Querying
-
Labels:None
-
Query
-
(copied to CRM)
Some query shape optimizations are not reflected in an obvious way by the cached plans. For example, the parser appears to transform a single value $in list into an equality when parsing the query:
> c.find({x:{$in:[3]}}).explain().queryPlanner.parsedQuery
{ "x" : { "$eq" : 3 } }
> c.find({x:{$in:[6,7]}}).explain().queryPlanner.parsedQuery
{ "x" : { "$in" : [ 6, 7 ] } }
However, the shape reported by the plan cache does not visually reflect those optimizations. This means that the following situations can occur:
- Two entries will appear to have the same shape - one with a single value for the $in list and the other with a larger number of values.
- A single entry will be present reflecting both a direct equality query and an optimized single value $in list query. The representation will depend on variant of the shape present at the time it is cached.
> db.version()
3.6.2
> c.getPlanCache().listQueryShapes()
[ ]
> c.find({x:{$in:[3]}})
> c.getPlanCache().listQueryShapes()
[
{
"query" : {
"x" : {
"$in" : [
3
]
}
},
"sort" : {
},
"projection" : {
}
}
]
> c.find({x:5})
> c.getPlanCache().listQueryShapes()
[
{
"query" : {
"x" : {
"$in" : [
3
]
}
},
"sort" : {
},
"projection" : {
}
}
]
> c.find({x:{$in:[6,7]}})
> c.getPlanCache().listQueryShapes()
[
{
"query" : {
"x" : {
"$in" : [
6,
7
]
}
},
"sort" : {
},
"projection" : {
}
},
{
"query" : {
"x" : {
"$in" : [
3
]
}
},
"sort" : {
},
"projection" : {
}
}
]
> c.getPlanCache().clear()
> c.getPlanCache().listQueryShapes()
[ ]
> c.find({x:5})
> c.getPlanCache().listQueryShapes()
[
{
"query" : {
"x" : 5
},
"sort" : {
},
"projection" : {
}
}
]
> c.find({x:{$in:[3]}})
> c.getPlanCache().listQueryShapes()
[
{
"query" : {
"x" : 5
},
"sort" : {
},
"projection" : {
}
}
]
> c.find({x:{$in:[6,7]}})
> c.getPlanCache().listQueryShapes()
[
{
"query" : {
"x" : {
"$in" : [
6,
7
]
}
},
"sort" : {
},
"projection" : {
}
},
{
"query" : {
"x" : 5
},
"sort" : {
},
"projection" : {
}
}
]
For completeness for this specific example, other changes in the length of the $in list appear to have the same query shape as intuitively expected:
>c.getPlanCache().clear()
> c.find({x:{$in:[6,7]}})
> c.getPlanCache().listQueryShapes()
[
{
"query" : {
"x" : {
"$in" : [
6,
7
]
}
},
"sort" : {
},
"projection" : {
}
}
]
> c.find({x:{$in:[8,9,10]}})
> c.getPlanCache().listQueryShapes()
[
{
"query" : {
"x" : {
"$in" : [
6,
7
]
}
},
"sort" : {
},
"projection" : {
}
}
]
- duplicates
-
-
- Closed
-
- related to
-
-
- Closed
-