-
Type:
Bug
-
Resolution: Done
-
Priority:
Major - P3
-
None
-
Affects Version/s: 2.6.0
-
Component/s: Querying
-
None
-
ALL
-
None
-
None
-
None
-
None
-
None
-
None
-
None
MongoDB 2.6.0 fails to use any index when range querying field A and sorting field B. Both fields are indexed. Both fields are Date.
Given a compound index
{voidTime: 1, movementTime: 1}all these 4 queries should be able to use this index:
1. db.stockReservation.find({ "voidTime" : {$in: [ISODate('2014-05-05T01:22:12.580Z'), ISODate('2014-05-05T01:29:28.528Z'), ISODate('2014-05-05T01:30:55.111Z')]}}) .explain()
2. db.stockReservation.find({ "voidTime" : {$in: [ISODate('2014-05-05T01:22:12.580Z'), ISODate('2014-05-05T01:29:28.528Z'), ISODate('2014-05-05T01:30:55.111Z')]}}) .sort({movementTime: 1}).explain()
3. db.stockReservation.find({ "voidTime" : { "$gte" : ISODate("2014-05-04T17:00:00.000Z") , "$lt" : ISODate("2014-05-05T10:20:13.894Z")}}) .explain()
4. db.stockReservation.find({ "voidTime" : { "$gte" : ISODate("2014-05-04T17:00:00.000Z") , "$lt" : ISODate("2014-05-05T10:20:13.894Z")}}) .sort({movementTime: 1}) .explain()
However MongoDB 2.6.0 fails to use index when sorting for case #4.
This did not happen to MongoDB 2.4.x. This is similar to SERVER-13611. However SERVER-13611 also happens to a "simple" query, while this bug only happens for range queries (i.e. $gte/$gt/$lte/$lt).
Explain results :
bippo:PRIMARY> db.stockReservation.ensureIndex({voidTime: 1, movementTime:1})
{ "numIndexesBefore" : 23, "note" : "all indexes already exist", "ok" : 1 }
bippo:PRIMARY> db.stockReservation.find({ "voidTime" : {$in: [ISODate('2014-05-05T01:22:12.580Z'), ISODate('2014-05-05T01:29:28.528Z'), ISODate('2014-05-05T01:30:55.111Z')]}}) .explain()
{
"cursor" : "BtreeCursor voidTime_1_movementTime_1",
"isMultiKey" : false,
"n" : 3,
"nscannedObjects" : 3,
"nscanned" : 3,
"nscannedObjectsAllPlans" : 6,
"nscannedAllPlans" : 6,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"voidTime" : [
[
ISODate("2014-05-05T01:22:12.580Z"),
ISODate("2014-05-05T01:22:12.580Z")
],
[
ISODate("2014-05-05T01:29:28.528Z"),
ISODate("2014-05-05T01:29:28.528Z")
],
[
ISODate("2014-05-05T01:30:55.111Z"),
ISODate("2014-05-05T01:30:55.111Z")
]
],
"movementTime" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
},
"filterSet" : false
}
bippo:PRIMARY> db.stockReservation.find({ "voidTime" : {$in: [ISODate('2014-05-05T01:22:12.580Z'), ISODate('2014-05-05T01:29:28.528Z'), ISODate('2014-05-05T01:30:55.111Z')]}}) .sort({movementTime: 1}).explain()
{
"clauses" : [
{
"cursor" : "BtreeCursor voidTime_1_movementTime_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"voidTime" : [
[
ISODate("2014-05-05T01:22:12.580Z"),
ISODate("2014-05-05T01:22:12.580Z")
]
],
"movementTime" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
},
{
"cursor" : "BtreeCursor voidTime_1_movementTime_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"voidTime" : [
[
ISODate("2014-05-05T01:29:28.528Z"),
ISODate("2014-05-05T01:29:28.528Z")
]
],
"movementTime" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
},
{
"cursor" : "BtreeCursor voidTime_1_movementTime_1",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nChunkSkips" : 0,
"indexBounds" : {
"voidTime" : [
[
ISODate("2014-05-05T01:30:55.111Z"),
ISODate("2014-05-05T01:30:55.111Z")
]
],
"movementTime" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
}
}
],
"cursor" : "QueryOptimizerCursor",
"n" : 3,
"nscannedObjects" : 3,
"nscanned" : 3,
"nscannedObjectsAllPlans" : 6,
"nscannedAllPlans" : 6,
"scanAndOrder" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"filterSet" : false
}
bippo:PRIMARY> db.stockReservation.find({ "voidTime" : { "$gte" : ISODate("2014-05-04T17:00:00.000Z") , "$lt" : ISODate("2014-05-05T10:20:13.894Z")}}) .explain()
{
"cursor" : "BtreeCursor voidTime_1_movementTime_1",
"isMultiKey" : false,
"n" : 342,
"nscannedObjects" : 342,
"nscanned" : 342,
"nscannedObjectsAllPlans" : 443,
"nscannedAllPlans" : 444,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 2,
"nChunkSkips" : 0,
"millis" : 1,
"indexBounds" : {
"voidTime" : [
[
ISODate("2014-05-04T17:00:00Z"),
ISODate("2014-05-05T10:20:13.894Z")
]
],
"movementTime" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
},
"filterSet" : false
}
bippo:PRIMARY> db.stockReservation.find({ "voidTime" : { "$gte" : ISODate("2014-05-04T17:00:00.000Z") , "$lt" : ISODate("2014-05-05T10:20:13.894Z")}}) .sort({movementTime: 1}) .explain()
{
"cursor" : "BtreeCursor movementTime_1",
"isMultiKey" : false,
"n" : 342,
"nscannedObjects" : 43029,
"nscanned" : 43029,
"nscannedObjectsAllPlans" : 43674,
"nscannedAllPlans" : 43675,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 340,
"nChunkSkips" : 0,
"millis" : 119,
"indexBounds" : {
"movementTime" : [
[
{
"$minElement" : 1
},
{
"$maxElement" : 1
}
]
]
},
"filterSet" : false
}
bippo:PRIMARY>
With data too big, this will give error:
"Runner error: Overflow sort stage buffered data usage of X bytes exceeds internal limit of 33554432 bytes"
But even when error not occurred, the query will be very inefficient.
- related to
-
-
- Backlog
-