-
Type:
Bug
-
Resolution: Won't Fix
-
Priority:
Major - P3
-
None
-
Affects Version/s: 2.4.12
-
Component/s: Querying
-
None
-
Fully Compatible
-
ALL
-
None
-
None
-
None
-
None
-
None
-
None
-
None
In a very specific case the 2.4 query optimizer doesn't use index bounds correctly.
db.ggg.drop()
db.ggg.insert({party:[{a:5,b:10},{a:3,b:2}]})
db.ggg.insert({party:[{a:5,b:10},{a:4,b:3}]})
db.ggg.insert({party:[{a:5,b:0},{a:4,b:3}]})
db.ggg.insert({party:[{a:1,b:0},{a:4,b:3}]})
db.ggg.insert({party:[{a:0,b:1},{a:2,b:0}]})
db.ggg.ensureIndex({"party.a":1,"party.b":1})
Expected behavior - two index used with correct bounds:
> db.ggg.find({ $or: [ { "party": { $elemMatch: { a: 5, b:10 } } }, { "party": { $elemMatch: { a: 6, b:11 } } } ] }).explain()
{
"clauses" : [
{
"cursor" : "BtreeCursor party.a_1_party.b_1",
"isMultiKey" : true,
"n" : 2,
"nscannedObjects" : 2,
"nscanned" : 2,
"nscannedObjectsAllPlans" : 2,
"nscannedAllPlans" : 2,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"party.a" : [
[
5,
5
]
],
"party.b" : [
[
10,
10
]
]
}
},
{
"cursor" : "BtreeCursor party.a_1_party.b_1",
"isMultiKey" : true,
"n" : 0,
"nscannedObjects" : 0,
"nscanned" : 0,
"nscannedObjectsAllPlans" : 0,
"nscannedAllPlans" : 0,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"party.a" : [
[
6,
6
]
],
"party.b" : [
[
11,
11
]
]
}
}
],
"n" : 2,
"nscannedObjects" : 2,
"nscanned" : 2,
"nscannedObjectsAllPlans" : 2,
"nscannedAllPlans" : 2,
"millis" : 0,
"server" : "AD-MAC10G.local:27017"
}
However, if I change the second clause to use the same value of "a", the query optimizer leaves out the bounds for "b":
> db.ggg.find({ $or: [ { "party": { $elemMatch: { a: 5, b:10 } } }, { "party": { $elemMatch: { a: 5, b:11 } } } ] }).explain()
{
"clauses" : [
{
"cursor" : "BtreeCursor party.a_1_party.b_1",
"isMultiKey" : true,
"n" : 2,
"nscannedObjects" : 2,
"nscanned" : 2,
"nscannedObjectsAllPlans" : 2,
"nscannedAllPlans" : 2,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"party.a" : [
[
5,
5
]
],
"party.b" : [
[
10,
10
]
]
}
},
{
"cursor" : "BtreeCursor party.a_1_party.b_1",
"isMultiKey" : true,
"n" : 0,
"nscannedObjects" : 3,
"nscanned" : 3,
"nscannedObjectsAllPlans" : 3,
"nscannedAllPlans" : 3,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"party.a" : [
[
5,
5
]
],
"party.b" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
}
],
"n" : 2,
"nscannedObjects" : 5,
"nscanned" : 5,
"nscannedObjectsAllPlans" : 5,
"nscannedAllPlans" : 5,
"millis" : 0,
"server" : "AD-MAC10G.local:27017"
}
>
The issue is not present in 2.6, as of 2.6.5.