Details
-
Bug
-
Resolution: Duplicate
-
Minor - P4
-
None
-
4.0.13
-
None
-
ALL
-
Description
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
|
}
|
|
Attachments
Issue Links
- duplicates
-
SERVER-23406 index scan is slower than full collection scan in some scenarios
-
- Backlog
-