-
Type: Bug
-
Resolution: Duplicate
-
Priority: Minor - P4
-
None
-
Affects Version/s: 4.0.13
-
Component/s: Querying
-
None
-
ALL
-
I have a collection with over 2 million entries in it. I allow find and sort on both indexed and non-indexed fields.
When I do a find and sort using only non-index fields it takes a few seconds and the query plan looks like this
/* 1 */ { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "apc.radios", "indexFilterSet" : false, "parsedQuery" : { "lifecycleState" : { "$eq" : "INVENTORY" } }, "winningPlan" : { "stage" : "SORT", "sortPattern" : { "lifecycleState" : 1.0 }, "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "inputStage" : { "stage" : "COLLSCAN", "filter" : { "lifecycleState" : { "$eq" : "INVENTORY" } }, "direction" : "forward" } } }, "rejectedPlans" : [] }, "executionStats" : { "executionSuccess" : true, "nReturned" : 111, "executionTimeMillis" : 1962, "totalKeysExamined" : 0, "totalDocsExamined" : 2202676, "executionStages" : { "stage" : "SORT", "nReturned" : 111, "executionTimeMillisEstimate" : 1799, "works" : 2202791, "advanced" : 111, "needTime" : 2202679, "needYield" : 0, "saveState" : 17210, "restoreState" : 17210, "isEOF" : 1, "invalidates" : 0, "sortPattern" : { "lifecycleState" : 1.0 }, "memUsage" : 38922, "memLimit" : 33554432, "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "nReturned" : 111, "executionTimeMillisEstimate" : 1709, "works" : 2202679, "advanced" : 111, "needTime" : 2202567, "needYield" : 0, "saveState" : 17210, "restoreState" : 17210, "isEOF" : 1, "invalidates" : 0, "inputStage" : { "stage" : "COLLSCAN", "filter" : { "lifecycleState" : { "$eq" : "INVENTORY" } }, "nReturned" : 111, "executionTimeMillisEstimate" : 1599, "works" : 2202678, "advanced" : 111, "needTime" : 2202566, "needYield" : 0, "saveState" : 17210, "restoreState" : 17210, "isEOF" : 1, "invalidates" : 0, "direction" : "forward", "docsExamined" : 2202676 } } }, "allPlansExecution" : [] }, "serverInfo" : { "host" : "netmet-mongo-d86c5c5bb-vd89w", "port" : 27017, "version" : "4.0.9", "gitVersion" : "fc525e2d9b0e4bceff5c2201457e564362909765" }, "ok" : 1.0 }
When I do a find on a non-indexed field but sort on an indexed field it is significantly slower which is not what I would expect. I would expect it to be the same or faster then the previous query since it should be using the index for the sort. Here is the query plan
/* 1 */ { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "apc.radios", "indexFilterSet" : false, "parsedQuery" : { "lifecycleState" : { "$eq" : "INVENTORY" } }, "winningPlan" : { "stage" : "FETCH", "filter" : { "lifecycleState" : { "$eq" : "INVENTORY" } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "repId" : 1 }, "indexName" : "repId_1", "isMultiKey" : false, "multiKeyPaths" : { "repId" : [] }, "isUnique" : true, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "repId" : [ "[MinKey, MaxKey]" ] } } }, "rejectedPlans" : [] }, "executionStats" : { "executionSuccess" : true, "nReturned" : 111, "executionTimeMillis" : 9362, "totalKeysExamined" : 2202669, "totalDocsExamined" : 2202669, "executionStages" : { "stage" : "FETCH", "filter" : { "lifecycleState" : { "$eq" : "INVENTORY" } }, "nReturned" : 111, "executionTimeMillisEstimate" : 9070, "works" : 2202670, "advanced" : 111, "needTime" : 2202558, "needYield" : 0, "saveState" : 17213, "restoreState" : 17213, "isEOF" : 1, "invalidates" : 0, "docsExamined" : 2202669, "alreadyHasObj" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 2202669, "executionTimeMillisEstimate" : 1152, "works" : 2202670, "advanced" : 2202669, "needTime" : 0, "needYield" : 0, "saveState" : 17213, "restoreState" : 17213, "isEOF" : 1, "invalidates" : 0, "keyPattern" : { "repId" : 1 }, "indexName" : "repId_1", "isMultiKey" : false, "multiKeyPaths" : { "repId" : [] }, "isUnique" : true, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "repId" : [ "[MinKey, MaxKey]" ] }, "keysExamined" : 2202669, "seeks" : 1, "dupsTested" : 0, "dupsDropped" : 0, "seenInvalidated" : 0 } }, "allPlansExecution" : [] }, "serverInfo" : { "host" : "netmet-mongo-d86c5c5bb-vd89w", "port" : 27017, "version" : "4.0.9", "gitVersion" : "fc525e2d9b0e4bceff5c2201457e564362909765" }, "ok" : 1.0 }
- duplicates
-
SERVER-23406 index scan is slower than full collection scan in some scenarios
- Backlog