Group by aggregation not using index COUNT_SCAN

[Luca_Moroldo]

Hi everyone,

I am having trouble optimizing a MongoDB aggregation that I think should use an index COUNT_SCAN but is using an IXSCAN instead.

This is a document sample:

{
  "_id": {
    "$oid": "660c01962422df625d11e39d"
  },
  "tenant": "tenant",
  "groupId": "660bfed352c6d1415b893517",
  "currentState": "PROCESSED_WITH_ERRORS",
  "currentErrorCode": "RateLimitConfigNotFoundException"
}

where I have the index: tenant_1_groupId_1_currentState_1_currentErrorCode_1

Finally, this is my aggregation:

[
  {
    $match: {
      $and: [
        {
          tenant: "tenant"
        },
        {
          groupId: "660bfed352c6d1415b893517"
        },
        {
          currentState: "PROCESSED_WITH_ERRORS"
        }
      ]
    }
  },
  {
    $group: {
      _id: "$currentErrorCode",
      count: {
        $sum: 1
      }
    }
  }
]

that has this plan:

I need to optimize the aggregation so that it uses COUNT_SCAN on each $currentErrorCode.

Please note that:

• I am using MongoDB 6.0
• I do not know the values for such field beforehand, so I cannot explicitly filter & count
• With the following aggregation, a COUNT_SCAN is used:

[ { $match: { $and: [ {tenant: "tenant-inject-me-one-day"}, {groupId: "660bfed352c6d1415b893517"}, {currentState: "PROCESSED_WITH_ERRORS"}, {currentErrorCode: "RateLimitConfigNotFoundException"} ]}}, { $count: 'total' } ]

Thank you for any idea