|
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.
|