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

Poor performance on certain configurations of hint/limit/min/max

    • ALL

       I've run into a performance issue when using min/limit/hint without max that I'd like some insight on.

      I've got an index called sort_updated_at on my collection:

      { client_id: 1, updated_at: 1, _id: 1 }

      Note: there are 15 total entries in the db with client_id : "cl_cjr7ucmwi00030xqip5ez34ym"

      If I make the following query

      db.appointments.find(
        {client_id: 'cl_cjr7ucmwi00030xqip5ez34ym'}
      )
      .sort({updated_at: 1})
      .hint('sort_updated_at')
      .min({
          client_id : "cl_cjr7ucmwi00030xqip5ez34ym",
          updated_at : ISODate("2019-03-15T17:04:08.095+0000"), 
          _id : ObjectId("5c8bdb07553f1800058a8ab0"), 
      })
      .max({ 
          client_id : "cl_cjr7ucmwi00030xqip5ez34ym", 
          updated_at : MaxKey, 
          _id : MaxKey, 
      })
      .limit(20);
      

      This has very good performance

              "nReturned": 15.0,
              "executionTimeMillis": 19.0,
              "totalKeysExamined": 15.0,
              "totalDocsExamined": 15.0,
      

      (explain1)

      However, if I remove the max configuration I get the following despite the client_id restriction in the original query:

              "nReturned" : 15.0, 
              "executionTimeMillis" : 7582.0, 
              "totalKeysExamined" : 116046.0, 
              "totalDocsExamined" : 116046.0, 
      

      (explain2)

      Finally, if I also remove the min configuration I see the performance improve again

          "nReturned" : 15.0, 
          "executionTimeMillis" : 13.0, 
          "totalKeysExamined" : 15.0, 
          "totalDocsExamined" : 15.0, 
      

      (explain3)

      I've attached {[explain}} results for each of the above three scenarios using mongodb 3.4.20 and 4.0.3 as well as the corresponding server info.

      What am I missing?

        1. explain1_34.json
          5 kB
          Ryan Keener
        2. explain1_40.json
          5 kB
          Ryan Keener
        3. explain2_34.json
          6 kB
          Ryan Keener
        4. explain2_40.json
          6 kB
          Ryan Keener
        5. explain3_34.json
          5 kB
          Ryan Keener
        6. explain3_40.json
          5 kB
          Ryan Keener
        7. server_34.json
          2 kB
          Ryan Keener
        8. server_40.json
          2 kB
          Ryan Keener

            Assignee:
            eric.sedor@mongodb.com Eric Sedor
            Reporter:
            bendru Ryan Keener
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: