-
Type:
Bug
-
Resolution: Done
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
ALL
-
None
-
None
-
None
-
None
-
None
-
None
-
None
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
}