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

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

    • Type: Icon: Bug Bug
    • Resolution: Gone away
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 3.4.9
    • Component/s: Querying
    • None
    • Query
    • ALL

      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);
      
      

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

            Assignee:
            backlog-server-query Backlog - Query Team (Inactive)
            Reporter:
            alessandro.gherardi@yahoo.com Alessandro Gherardi
            Votes:
            1 Vote for this issue
            Watchers:
            7 Start watching this issue

              Created:
              Updated:
              Resolved: