-
Type: Bug
-
Resolution: Done
-
Priority: Major - P3
-
None
-
Affects Version/s: 2.6.0
-
Component/s: Querying
-
None
-
ALL
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
-
SERVER-12923 Plan ranking is bad for plans with blocking stages
- Backlog