Details
-
Improvement
-
Status: Closed
-
Major - P3
-
Resolution: Duplicate
-
None
-
None
-
None
-
Query
-
(copied to CRM)
Description
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" : {
|
|
}
|
}
|
]
|
Attachments
Issue Links
- duplicates
-
-
- Closed
-
- related to
-
-
- Closed
-