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

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

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major - P3
    • Resolution: Gone away
    • 3.4.9
    • None
    • Querying
    • None
    • Query
    • 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

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

              Dates

                Created:
                Updated:
                Resolved: