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

Sort with limit/skip hits memory limit

    XMLWordPrintableJSON

Details

    • Icon: Question Question
    • Resolution: Done
    • Icon: Major - P3 Major - P3
    • None
    • 3.2.4
    • Index Maintenance, Querying
    • None

    Description

      We have a complex compound index that is used on a large(ish) data set to satisfy a query along with a sort. The compound follows the ESR (equality, sort, and ranged) ordering and does get used when queries are made. However... when sorting and then using limit/skip we hit the server memory limit for sorts which suggests the index is not used for the sort. So the question here is about if the index is being used for the sort correctly, and more specifically are we doing something wrong here or is there a server bug?

      db.collection.find({
        "companyId" : "1",
        "deletedAt" : { "$exists" : false },
        "flags.archived" : { "$ne" : true }
       
      }).sort({"createdAt":1}).skip(45000).limit(1).explain();
      

      Using explain we see the following winning plan:

      "winningPlan" : {
                  "stage" : "SKIP", 
                  "skipAmount" : NumberInt(45000), 
                  "inputStage" : {
                      "stage" : "SORT", 
                      "sortPattern" : {
                          "createdAt" : NumberInt(1)
                      }, 
                      "limitAmount" : NumberInt(45001), 
                      "inputStage" : {
                          "stage" : "SORT_KEY_GENERATOR", 
                          "inputStage" : {
                              "stage" : "FETCH", 
                              "filter" : {
                                  "$and" : [
                                      {
                                          "$not" : {
                                              "flags.archived" : {
                                                  "$eq" : true
                                              }
                                          }
                                      }, 
                                      {
                                          "$not" : {
                                              "deletedAt" : {
                                                  "$exists" : true
                                              }
                                          }
                                      }
                                  ]
                              }, 
                              "inputStage" : {
                                  "stage" : "IXSCAN", 
                                  "keyPattern" : {
                                      "companyId" : NumberInt(1), 
                                      "assignedTo" : NumberInt(1), 
                                      "deletedAt" : NumberInt(1), 
                                      "emails.value" : NumberInt(1), 
                                      "flags.archived" : NumberInt(1), 
                                      "firstName" : NumberInt(1), 
                                      "fullName" : NumberInt(1), 
                                      "lastName" : NumberInt(1), 
                                      "source" : NumberInt(1), 
                                      "status" : NumberInt(1), 
                                      "statusValues" : NumberInt(1), 
                                      "sourceValues" : NumberInt(1), 
                                      "daysInStatus" : NumberInt(1), 
                                      "lastContact" : NumberInt(1), 
                                      "lastActivity" : NumberInt(1), 
                                      "createdAt" : NumberInt(1)
                                  }, 
                                  "indexName" : "companyId_Compound", 
                                  "isMultiKey" : true, 
                                  "isUnique" : false, 
                                  "isSparse" : false, 
                                  "isPartial" : false, 
                                  "indexVersion" : NumberInt(1), 
                                  "direction" : "forward", 
                                  "indexBounds" : {
                                      "companyId" : [
                                          "[\"1\", \"1\"]"
                                      ], 
                                      "assignedTo" : [
                                          "[MinKey, MaxKey]"
                                      ], 
                                      "deletedAt" : [
                                          "[null, null]"
                                      ], 
                                      "emails.value" : [
                                          "[MinKey, MaxKey]"
                                      ], 
                                      "flags.archived" : [
                                          "[MinKey, true)", 
                                          "(true, MaxKey]"
                                      ], 
                                      "firstName" : [
                                          "[MinKey, MaxKey]"
                                      ], 
                                      "fullName" : [
                                          "[MinKey, MaxKey]"
                                      ], 
                                      "lastName" : [
                                          "[MinKey, MaxKey]"
                                      ], 
                                      "source" : [
                                          "[MinKey, MaxKey]"
                                      ], 
                                      "status" : [
                                          "[MinKey, MaxKey]"
                                      ], 
                                      "statusValues" : [
                                          "[MinKey, MaxKey]"
                                      ], 
                                      "sourceValues" : [
                                          "[MinKey, MaxKey]"
                                      ], 
                                      "daysInStatus" : [
                                          "[MinKey, MaxKey]"
                                      ], 
                                      "lastContact" : [
                                          "[MinKey, MaxKey]"
                                      ], 
                                      "lastActivity" : [
                                          "[MinKey, MaxKey]"
                                      ], 
                                      "createdAt" : [
                                          "[MinKey, MaxKey]"
                                      ]
                                  }
                              }
                          }
                      }
                  }
              }
      

      Attachments

        Activity

          People

            kelsey.schubert@mongodb.com Kelsey Schubert
            Crispy1975 Paul C [X]
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: