[SERVER-71432] mongodb 5.0 $group nulll and $field indicates different execution plan Created: 17/Nov/22  Updated: 18/Nov/22  Resolved: 18/Nov/22

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: jing xu Assignee: Yuan Fang
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Participants:

 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
}



 Comments   
Comment by Yuan Fang [ 18/Nov/22 ]

Hi, 601290552@qq.com,

Thank you for your report. For this issue, we'd like to encourage you to start by asking our community for help by posting on the MongoDB Developer Community Forums.

If the discussion there leads you to suspect a bug in the MongoDB server, then we'd want to investigate it as a possible bug here in the SERVER project.

Regards,
Yuan

Generated at Thu Feb 08 06:18:59 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.