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

Implementing paging using range queries, latency of last query is much higher

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Gone away
    • Affects Version/s: 3.4.9
    • Fix Version/s: None
    • Component/s: Querying
    • Labels:
      None
    • Operating System:
      ALL

      Description

      I'm trying to implement paging using range queries. I have a collection called events that has, among others, a time attribute (type date).

      I defined a compound index with key

      {"time" : 1, "_id" : 1}

      . I use the script below to retrieve the events in pages.

      The query latency is very good (< 20 ms) for all queries except the last one. An explain shows that, unlike the other queries, the last query does not use the compound index. It looks like this has something to do with the value of startTime being equal to or very close to the value of lastTime.

      var startTime = ISODate("2017-09-18T00:00:00Z");
      var endTime = ISODate("2017-09-18T00:30:00Z");
      var pageSize = 555;
      var maxTimeMS = 6000;
      var dbName = "foo";
       
      var totalLatency = 0;
      var skip = 0;
      var lastTime = null;
      var lastEventId = null;
      var firstPage = true;
      while (true) {
        var start = new Date();
        var eventsRead = 0;
        if (firstPage) {
          firstPage = false;
       
          db.getSiblingDB(dbName).events.find({ time: { $gte: startTime, $lte: endTime}}).sort({ "time" : -1 , "_id" : -1}).limit(pageSize).maxTimeMS(maxTimeMS).forEach(function(s) {
            eventsRead++;
            lastTime = s.time;
            lastEventId = s._id;
          } );
        } else {
          db.getSiblingDB(dbName).events.find({ time: { $gte: startTime, $lte: endTime}, "$or" : [ { "time" : { "$lt" : lastTime}} , { "time" : lastTime , "_id" : { "$lt" : lastEventId }} ] }).sort({ "time" : -1 , "_id" : -1}).limit(pageSize).maxTimeMS(maxTimeMS).forEach(function(s) {
            eventsRead++;
            lastTime = s.time;
            lastEventId = s._id;
          } );
        }
        var elapsed = new Date() - start;
        totalLatency += elapsed;
        skip = skip + eventsRead;
        print("page " + skip + ", elapsed " + elapsed);
        if (eventsRead < pageSize) {
          break;
        }
      }
       
      print("Total events read: " + skip + ", total latency: " + totalLatency);
      
      

        Attachments

        1. explain.1
          161 kB
        2. explain.2
          161 kB

          Issue Links

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                9 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: