-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: Querying
-
Labels:
-
Query Optimization
-
(copied to CRM)
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.