Mongodb 5.0 $group nulll and $field indicates different execution 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
}

the difference is the covered query:

–this genergate not covered query cause poor performance

db.xiaoxu.aggregate([{ $match:{fld4:null}},{$group:{_id:"$fld4",total:{$sum:1}}}])

–this genergate covered query

db.xiaoxu.aggregate([{ $match:{fld4:null}},{$group:{_id:null,total:{$sum:1}}}])