Details
-
Improvement
-
Resolution: Unresolved
-
Major - P3
-
None
-
None
-
Query Optimization
-
(copied to CRM)
Description
Sample data:
db.elem.find()
|
{ "_id" : ObjectId("5967e1cbb6a1aa8d363befdd"), "a1" : [ { "id" : "a", "a2" : [ { "s" : 23 }, { "s" : 28 }, { "s" : 30 } ] } ] }
|
{ "_id" : ObjectId("5967e1d2b6a1aa8d363befde"), "a1" : [ { "id" : "b", "a2" : [ { "s" : 23 }, { "s" : 28 }, { "s" : 30 } ] } ] }
|
And index on "a1.id":1, "a1.a2.s":1 I would expect the query below to use tight bounds on "a1.id" and on "a1.a2.s" but it uses Minkey,Maxkey on "a1.a2.s":
db.elem.find({a1:{$elemMatch:{ id: "a", a2:{$elemMatch:{s:{$gt:25,$lt:29}}}}}}).explain(true)
|
{
|
"queryPlanner" : {
|
"plannerVersion" : 1,
|
"namespace" : "test.elem",
|
"indexFilterSet" : false,
|
"parsedQuery" : {
|
"a1" : {
|
"$elemMatch" : {
|
"$and" : [
|
{
|
"a2" : {
|
"$elemMatch" : {
|
"$and" : [
|
{
|
"s" : {
|
"$lt" : 29
|
}
|
},
|
{
|
"s" : {
|
"$gt" : 25
|
}
|
}
|
]
|
}
|
}
|
},
|
{
|
"id" : {
|
"$eq" : "a"
|
}
|
}
|
]
|
}
|
}
|
},
|
"winningPlan" : {
|
"stage" : "FETCH",
|
"filter" : {
|
"a1" : {
|
"$elemMatch" : {
|
"$and" : [
|
{
|
"id" : {
|
"$eq" : "a"
|
}
|
},
|
{
|
"a2" : {
|
"$elemMatch" : {
|
"$and" : [
|
{
|
"s" : {
|
"$lt" : 29
|
}
|
},
|
{
|
"s" : {
|
"$gt" : 25
|
}
|
}
|
]
|
}
|
}
|
}
|
]
|
}
|
}
|
},
|
"inputStage" : {
|
"stage" : "IXSCAN",
|
"keyPattern" : {
|
"a1.id" : 1,
|
"a1.a2.s" : 1
|
},
|
"indexName" : "a1.id_1_a1.a2.s_1",
|
"isMultiKey" : true,
|
"multiKeyPaths" : {
|
"a1.id" : [
|
"a1"
|
],
|
"a1.a2.s" : [
|
"a1",
|
"a1.a2"
|
]
|
},
|
"isUnique" : false,
|
"isSparse" : false,
|
"isPartial" : false,
|
"indexVersion" : 2,
|
"direction" : "forward",
|
"indexBounds" : {
|
"a1.id" : [
|
"[\"a\", \"a\"]"
|
],
|
"a1.a2.s" : [
|
"[MinKey, MaxKey]"
|
]
|
}
|
}
|
},
|
"rejectedPlans" : [ ]
|
}
|
If the data is structured with a scalar in a2 (or any other way such that the query on the indexed field is of form {{$elemMatch:{$gt: , $lt }}} then the tight bounds are used.