Index not used properly on aggregation with $or

Working with Data
,
4

Verified, indeed it is. I was thinking of index usage of sort which requires fields to be in the same order and direction; or only uses it partially if not all left-most fields are used.

TIL for fields which aren’t part of the check, it uses indexBounds MinKey, MaxKey and then uses actual values for other fields. Like here for “TenantId AND LifecycleState AND State AND ExternalReferenceId1” (skipping Name and Username):

{
  "stage": "IXSCAN",
  "filter": {
    "ExternalReferenceId1": {
      "$regex": "map"
    }
  },
  // ...
  "indexName": "TenantId_1_LifecycleState_1_State_1_Name_1_Username_1_ExternalReferenceId1_1_ExternalReferenceId2_1",
  "indexBounds": {
    "TenantId": [
      "[\"tenant1\", \"tenant1\"]"
    ],
    "LifecycleState": [
      "[\"Alive\", \"Alive\"]"
    ],
    "State": ["[\"open\", \"open\"]"],
    "Name": ["[MinKey, MaxKey]"],
    "Username": ["[MinKey, MaxKey]"],
    "ExternalReferenceId1": [
      "[\"\", {})",
      "[/map/, /map/]"
    ],
    "ExternalReferenceId2": [
      "[MinKey, MaxKey]"
    ]
  }
}