-
Type:
Question
-
Resolution: Done
-
Priority:
Major - P3
-
None
-
Affects Version/s: 3.2.4
-
Component/s: Index Maintenance, Querying
-
None
-
None
-
0
-
None
-
None
-
None
-
None
-
None
-
None
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]" ] } } } } } }