Details
-
Bug
-
Status: Closed
-
Major - P3
-
Resolution: Works as Designed
-
2.4.6, 2.4.7
-
None
-
None
-
ALL
Description
I have collection with about 4MM documents. There are two multikey indexes on two fields that are not present in all documents. The two indexes have the second field in a different direction.
i.e.
db.test_index.ensureIndex({"a.b" : 1, "a.c" : 1})
|
db.test_index.ensureIndex({"a.b" : 1, "a.c" : -1})
|
The result of doing a find, where the fields are null, against each index is dramatically different. (1 object scanned vs 1,055,153)
> db.test_index.find({"a.b": null, "a.c": null }, {"a.b": 1, "a.c": 1 }).hint({"a.b": 1, "a.c": 1 }).limit(1).explain()
|
{
|
"cursor" : "BtreeCursor a.b_1_a.c_1",
|
"isMultiKey" : true,
|
"n" : 1,
|
"nscannedObjects" : 1,
|
"nscanned" : 1,
|
"nscannedObjectsAllPlans" : 1,
|
"nscannedAllPlans" : 1,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"millis" : 0,
|
"indexBounds" : {
|
"a.b" : [
|
[
|
null,
|
null
|
]
|
],
|
"a.c" : [
|
[
|
{
|
"$minElement" : 1
|
},
|
{
|
"$maxElement" : 1
|
}
|
]
|
]
|
}
|
}
|
|
|
> db.test_index.find({"a.b": null, "a.c": null }, {"a.b": 1, "a.c": 1 }).hint({"a.b": 1, "a.c": -1 }).limit(1).explain()
|
{
|
"cursor" : "BtreeCursor a.b_1_a.c_-1",
|
"isMultiKey" : true,
|
"n" : 1,
|
"nscannedObjects" : 1055153,
|
"nscanned" : 1055153,
|
"nscannedObjectsAllPlans" : 1055153,
|
"nscannedAllPlans" : 1055153,
|
"scanAndOrder" : false,
|
"indexOnly" : false,
|
"nYields" : 0,
|
"nChunkSkips" : 0,
|
"millis" : 177775,
|
"indexBounds" : {
|
"a.b" : [
|
[
|
null,
|
null
|
]
|
],
|
"a.c" : [
|
[
|
{
|
"$maxElement" : 1
|
},
|
{
|
"$minElement" : 1
|
}
|
]
|
]
|
}
|
}
|
The following is a short script to reproduce. After running this the 1 index will show one object scanned, while the -1 index will show two.
db.test_index.ensureIndex({"a.b" : 1, "a.c" : 1})
|
db.test_index.ensureIndex({"a.b" : 1, "a.c" : -1})
|
db.test_index.insert(
|
[
|
{
|
"_id" : ObjectId("5198fb0acfe8202c49ab389d"),
|
"a" : [
|
{
|
"c" : ISODate("2013-08-17T22:14:40Z"),
|
},
|
{
|
"b" : ObjectId("5175284b50e71b43f29cd447"),
|
"c" : ISODate("2013-08-17T22:15:18Z")
|
}
|
]
|
},
|
{ "_id" : ObjectId("4ec0cb4eb2a44b0a3bf6102d") },
|
{
|
"_id" : ObjectId("4ec9f21100ad3b3ee3ca927a"),
|
"a" : [
|
{
|
"b" : ObjectId("4f6c377f00aa296ff90001f3"),
|
"c" : ISODate("2012-08-05T13:52:49Z")
|
}
|
]
|
}
|
]
|
)
|
|
|
db.test_index.find({ "a.b" : null, "a.c" : null }, { "a.b" : 1, "a.c" : 1 }).hint({ "a.b" : 1, "a.c" : 1 }).limit(-1).explain()
|
db.test_index.find({ "a.b" : null, "a.c" : null }, { "a.b" : 1, "a.c" : 1 }).hint({ "a.b" : 1, "a.c" : -1 }).limit(-1).explain()
|