Details
-
Bug
-
Resolution: Done
-
Major - P3
-
None
-
2.6.0
-
None
-
ALL
Description
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.
Attachments
Issue Links
- related to
-
-
- Backlog
-