–this genergate not covered query cause poor performance
db.xiaoxu.aggregate([\{ $match: {fld4: null
}
},\{$group: {_id: "$fld4",total: {$sum: 1
}
}
}
])
{
"explainVersion": "1",
"stages": [
{
"$cursor": {
"queryPlanner": {
"namespace": "POCDB.xiaoxu",
"indexFilterSet": false,
"parsedQuery": {
"fld4": {
"$eq": null
}
},
"queryHash": "7937EE4F",
"planCacheKey": "C77A1A63",
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"winningPlan": {
"stage": "PROJECTION_SIMPLE",
"transformBy": {
"fld4": 1,
"_id": 0
},
"inputStage": {
"stage": "FETCH",
"filter": {
"fld4": {
"$eq": null
}
},
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"fld4": 1
},
"indexName": "fld4_1",
"isMultiKey": false,
"multiKeyPaths": {
"fld4": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"fld4": [
"[undefined, undefined]",
"[null, null]"
]
}
}
}
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 550000,
"executionTimeMillis": 1332,
"totalKeysExamined": 550001,
"totalDocsExamined": 550000,
"executionStages": {
"stage": "PROJECTION_SIMPLE",
"nReturned": 550000,
"executionTimeMillisEstimate": 158,
"works": 550001,
"advanced": 550000,
"needTime": 0,
"needYield": 0,
"saveState": 570,
"restoreState": 570,
"isEOF": 1,
"transformBy": {
"fld4": 1,
"_id": 0
},
"inputStage": {
"stage": "FETCH",
"filter": {
"fld4": {
"$eq": null
}
},
"nReturned": 550000,
"executionTimeMillisEstimate": 121,
"works": 550001,
"advanced": 550000,
"needTime": 0,
"needYield": 0,
"saveState": 570,
"restoreState": 570,
"isEOF": 1,
"docsExamined": 550000,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 550000,
"executionTimeMillisEstimate": 40,
"works": 550001,
"advanced": 550000,
"needTime": 0,
"needYield": 0,
"saveState": 570,
"restoreState": 570,
"isEOF": 1,
"keyPattern": {
"fld4": 1
},
"indexName": "fld4_1",
"isMultiKey": false,
"multiKeyPaths": {
"fld4": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"fld4": [
"[undefined, undefined]",
"[null, null]"
]
},
"keysExamined": 550001,
"seeks": 1,
"dupsTested": 0,
"dupsDropped": 0,
"indexDef": {
"indexName": "fld4_1",
"isMultiKey": false,
"multiKeyPaths": {
"fld4": []
},
"keyPattern": {
"fld4": 1
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"direction": "forward"
}
}
}
}
}
},
"nReturned": 550000,
"executionTimeMillisEstimate": 1217
},
{
"$group": {
"_id": "$fld4",
"total": {
"$sum": {
"$const": 1
}
}
},
"maxAccumulatorMemoryUsageBytes": {
"total": 72
},
"totalOutputDataSizeBytes": 229,
"usedDisk": false,
"nReturned": 1,
"executionTimeMillisEstimate": 1330
}
],
"serverInfo": {
"host": "vmt30129",
"port": 51001,
"version": "5.0.2",
"gitVersion": "6d9ec525e78465dcecadcff99cce953d380fedc8"
},
"serverParameters": {
"internalQueryFacetBufferSizeBytes": 104857600,
"internalQueryFacetMaxOutputDocSizeBytes": 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
"internalDocumentSourceGroupMaxMemoryBytes": 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
"internalQueryProhibitBlockingMergeOnMongoS": 0,
"internalQueryMaxAddToSetBytes": 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600
},
"command": {
"aggregate": "xiaoxu",
"pipeline": [
{
"$match": {
"fld4": null
}
},
{
"$group": {
"_id": "$fld4",
"total": {
"$sum": 1
}
}
}
],
"cursor": {},
"$db": "POCDB"
},
"ok": 1
}
–this genergate covered query
db.xiaoxu.aggregate([
{ $match: {fld4: null
}
},
{$group: {_id: null,total: {$sum: 1
}
}
}
])
{
"explainVersion": "1",
"stages": [
{
"$cursor": {
"queryPlanner": {
"namespace": "POCDB.xiaoxu",
"indexFilterSet": false,
"parsedQuery": {
"fld4": {
"$eq": null
}
},
"queryHash": "2B634F0D",
"planCacheKey": "FC6E7CF8",
"maxIndexedOrSolutionsReached": false,
"maxIndexedAndSolutionsReached": false,
"maxScansToExplodeReached": false,
"winningPlan": {
"stage": "OR",
"inputStages": [
{
"stage": "COUNT_SCAN",
"keyPattern": {
"fld4": 1
},
"indexName": "fld4_1",
"isMultiKey": false,
"multiKeyPaths": {
"fld4": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"indexBounds": {
"startKey": {
"fld4": undefined
},
"startKeyInclusive": true,
"endKey": {
"fld4": undefined
},
"endKeyInclusive": true
}
},
{
"stage": "COUNT_SCAN",
"keyPattern": {
"fld4": 1
},
"indexName": "fld4_1",
"isMultiKey": false,
"multiKeyPaths": {
"fld4": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"indexBounds": {
"startKey": {
"fld4": null
},
"startKeyInclusive": true,
"endKey": {
"fld4": null
},
"endKeyInclusive": true
}
}
]
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 550000,
"executionTimeMillis": 424,
"totalKeysExamined": 550002,
"totalDocsExamined": 0,
"executionStages": {
"stage": "OR",
"nReturned": 550000,
"executionTimeMillisEstimate": 60,
"works": 550002,
"advanced": 550000,
"needTime": 1,
"needYield": 0,
"saveState": 551,
"restoreState": 551,
"isEOF": 1,
"dupsTested": 550000,
"dupsDropped": 0,
"inputStages": [
{
"stage": "COUNT_SCAN",
"nReturned": 0,
"executionTimeMillisEstimate": 0,
"works": 1,
"advanced": 0,
"needTime": 0,
"needYield": 0,
"saveState": 551,
"restoreState": 551,
"isEOF": 1,
"keysExamined": 1,
"keyPattern": {
"fld4": 1
},
"indexName": "fld4_1",
"isMultiKey": false,
"multiKeyPaths": {
"fld4": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"indexBounds": {
"startKey": {
"fld4": undefined
},
"startKeyInclusive": true,
"endKey": {
"fld4": undefined
},
"endKeyInclusive": true
}
},
{
"stage": "COUNT_SCAN",
"nReturned": 550000,
"executionTimeMillisEstimate": 18,
"works": 550001,
"advanced": 550000,
"needTime": 0,
"needYield": 0,
"saveState": 551,
"restoreState": 551,
"isEOF": 1,
"keysExamined": 550001,
"keyPattern": {
"fld4": 1
},
"indexName": "fld4_1",
"isMultiKey": false,
"multiKeyPaths": {
"fld4": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"indexBounds": {
"startKey": {
"fld4": null
},
"startKeyInclusive": true,
"endKey": {
"fld4": null
},
"endKeyInclusive": true
}
}
]
}
}
},
"nReturned": 550000,
"executionTimeMillisEstimate": 362
},
{
"$group": {
"_id": {
"$const": null
},
"total": {
"$sum": {
"$const": 1
}
}
},
"maxAccumulatorMemoryUsageBytes": {
"total": 72
},
"totalOutputDataSizeBytes": 229,
"usedDisk": false,
"nReturned": 1,
"executionTimeMillisEstimate": 415
}
],
"serverInfo": {
"host": "vmt30129",
"port": 51001,
"version": "5.0.2",
"gitVersion": "6d9ec525e78465dcecadcff99cce953d380fedc8"
},
"serverParameters": {
"internalQueryFacetBufferSizeBytes": 104857600,
"internalQueryFacetMaxOutputDocSizeBytes": 104857600,
"internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
"internalDocumentSourceGroupMaxMemoryBytes": 104857600,
"internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
"internalQueryProhibitBlockingMergeOnMongoS": 0,
"internalQueryMaxAddToSetBytes": 104857600,
"internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600
},
"command": {
"aggregate": "xiaoxu",
"pipeline": [
{
"$match": {
"fld4": null
}
},
{
"$group": {
"_id": null,
"total": {
"$sum": 1
}
}
}
],
"cursor": {},
"$db": "POCDB"
},
"ok": 1
}