Details
-
Bug
-
Resolution: Done
-
Major - P3
-
None
-
None
-
None
-
None
-
ALL
Description
hi:
my mongodb is 5.0.when i use aggregate $group state for count.difference $group that difference executions plan.
--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
|
}
|