[SERVER-25221] Sort with limit/skip hits memory limit Created: 22/Jul/16  Updated: 30/Jul/16  Resolved: 27/Jul/16

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: 3.2.4
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: Paul C [X] Assignee: Kelsey Schubert
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:

 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]"
                                ]
                            }
                        }
                    }
                }
            }
        }



 Comments   
Comment by Paul C [X] [ 27/Jul/16 ]

Thanks for the reply, will direct further questions to the groups mentioned.

Comment by Kelsey Schubert [ 27/Jul/16 ]

Hi Crispy1975,

Thanks for the report - this is expected behavior. Consider a compound index {a:1,b:1}. The documents would be ordered in the index as

a b
1 1
1 2
2 1
2 2

As you can see, if the query includes a range of a values then the returned documents will not be sorted by the b field. Consequently, a sort stage is required. For more information, please review our documentation on sorting results with indexes.

To resolve this issue, I would recommend modifying your indexes.

Please note that SERVER project is for reporting bugs or feature suggestions for the MongoDB server. For MongoDB-related support discussion please post on the mongodb-users group or Stack Overflow with the mongodb tag. A question like this involving more discussion would be best posted on the mongodb-users group.

Kind regards,
Thomas

Comment by Paul C [X] [ 27/Jul/16 ]

Any thoughts on this, would love to think we have done something wrong, and find it's not a bug, and can get these sorts working.

Generated at Thu Feb 08 04:08:35 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.