Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-13831

Find with $gte/$gt/$lte/$lt on Date field and sorting on another field leads to unnecessary in-memory sort without using index

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 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.

            Assignee:
            rassi J Rassi
            Reporter:
            rudi Rudi Wijaya
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Created:
              Updated:
              Resolved: