-
Type:
Bug
-
Resolution: Done
-
Priority:
Major - P3
-
None
-
Affects Version/s: 2.4.6, 2.4.7
-
Component/s: Index Maintenance
-
None
-
ALL
-
None
-
3
-
None
-
None
-
None
-
None
-
None
-
None
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()