Simple aggregation query takes too long even with index

Phan_Kha · 2024-05-30T07:24:50.754Z

Hi guys,

I have a very straightforward aggregation query against a collection of 2.5 million documents. My query is indexed, but it’s taking too long to execute, even though the result is just one document and only one key was scanned. I’ve attached the execution plan. Can you help me identify the issue? The server is not under highload and can serve other queries properly

{
  "explainVersion": "2",
  "queryPlanner": {
    "namespace": "CasewhereData_AC_DM.CWCwAcXXXXXXXXX",
    "indexFilterSet": false,
    "parsedQuery": {
      "$and": [
        { "CaseState": { "$eq": "Created" } },
        { "IsDeleted": { "$eq": false } },
        {
          "RegistrationNumber": {
            "$eq": "DAXXXXX"
          }
        }
      ]
    },
    "collation": {
      "locale": "da",
      "caseLevel": false,
      "caseFirst": "off",
      "strength": 2,
      "numericOrdering": false,
      "alternate": "non-ignorable",
      "maxVariable": "punct",
      "normalization": false,
      "backwards": false,
      "version": "57.1"
    },
    "queryHash": "96061684",
    "planCacheKey": "CD280589",
    "optimizedPipeline": true,
    "maxIndexedOrSolutionsReached": false,
    "maxIndexedAndSolutionsReached": false,
    "maxScansToExplodeReached": false,
    "winningPlan": {
      "queryPlan": {
        "stage": "FETCH",
        "planNodeId": 2,
        "inputStage": {
          "stage": "IXSCAN",
          "planNodeId": 1,
          "keyPattern": {
            "CaseState": 1,
            "IsDeleted": 1,
            "RegistrationNumber": 1,
            "_id": 1
          },
          "indexName": "cw_Sort asc registration number",
          "collation": {
            "locale": "da",
            "caseLevel": false,
            "caseFirst": "off",
            "strength": 2,
            "numericOrdering": false,
            "alternate": "non-ignorable",
            "maxVariable": "punct",
            "normalization": false,
            "backwards": false,
            "version": "57.1"
          },
          "isMultiKey": false,
          "multiKeyPaths": {
            "CaseState": [],
            "IsDeleted": [],
            "RegistrationNumber": [],
            "_id": []
          },
          "isUnique": false,
          "isSparse": false,
          "isPartial": false,
          "indexVersion": 2,
          "direction": "forward",
          "indexBounds": {
            "CaseState": [
              "[CollationKey(0x2d4b31294f312f010b), CollationKey(0x2d4b31294f312f010b)]"
            ],
            "IsDeleted": ["[false, false]"],
            "RegistrationNumber": [
              "[CollationKey(0x2f291424121222010b), CollationKey(0x2f291424121222010b)]"
            ],
            "_id": ["[MinKey, MaxKey]"]
          }
        }
      },
      "slotBasedPlan": {
        "slots": "$$RESULT=s13 env: { s2 = Nothing (SEARCH_META), s3 = Timestamp(1716964580, 16) (CLUSTER_TIME), s12 = {\"CaseState\" : 1, \"IsDeleted\" : 1, \"RegistrationNumber\" : 1, \"_id\" : 1}, s1 = TimeZoneDatabase(Africa/Conakry...Africa/Cairo) (timeZoneDB), s7 = KS(3C2D4B31294F312F010B006E3C2F291424121222010B000A0104), s4 = 1716964580483 (NOW), s5 = Collator({\"locale\" : \"da\", \"caseLevel\" : false, \"caseFirst\" : \"off\", \"strength\" : 2, \"numericOrdering\" : false, \"alternate\" : \"non-ignorable\", \"maxVariable\" : \"punct\", \"normalization\" : false, \"backwards\" : false, \"version\" : \"57.1\"}) (collator), s8 = KS(3C2D4B31294F312F010B006E3C2F291424121222010B00F0FE04) }",
        "stages": "[2] nlj inner [] [s6, s9, s10, s11, s12] \n    left \n        [1] cfilter {(exists(s7) && exists(s8))} \n        [1] ixseek s7 s8 s11 s6 s9 s10 [] @\"eff5caef-9d8b-4325-8f40-fb1de5bc64b0\" @\"cw_Sort asc registration number\" true \n    right \n        [2] limit 1 \n        [2] seek s6 s13 s14 s9 s10 s11 s12 [] @\"eff5caef-9d8b-4325-8f40-fb1de5bc64b0\" true false \n"
      }
    },
	....
    ]
  },
  "command": {
    "aggregate": "CWCwAcXXXXXXXXX",
    "pipeline": [
      {
        "$match": {
          "IsDeleted": false,
          "CaseState": "Created",
          "RegistrationNumber": "DAXXXXX"
        }
      }
    ],
    "cursor": {},
    "collation": {
      "locale": "da",
      "strength": 2,
      "caseFirst": "off"
    },
    "maxTimeMS": 60000,
    "$db": "CasewhereData_AC_DM"
  },
  "serverInfo": {
    "host": "ACMongoDB",
    "port": 27017,
    "version": "7.0.3",
    "gitVersion": "b96efb7e0cf6134d5938de8a94c37cec3f22cff4"
  },
  "serverParameters": {
    "internalQueryFacetBufferSizeBytes": 104857600,
    "internalQueryFacetMaxOutputDocSizeBytes": 104857600,
    "internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
    "internalDocumentSourceGroupMaxMemoryBytes": 104857600,
    "internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
    "internalQueryProhibitBlockingMergeOnMongoS": 0,
    "internalQueryMaxAddToSetBytes": 104857600,
    "internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600,
    "internalQueryFrameworkControl": "trySbeEngine"
  },
  "ok": 1,
  "$clusterTime": {
    "clusterTime": {
      "$timestamp": "7374307204821680148"
    },
    "signature": {
      "hash": "J1ClyCWMoQvvLbpfqaMRluW+tf8=",
      "keyId": {
        "low": 2,
        "high": 1708514212,
        "unsigned": false
      }
    }
  },
  "operationTime": {
    "$timestamp": "7374307204821680148"
  }
}

Peter_Hubbard · 2024-05-30T11:16:28.159Z

You might look at the order of the fields in the index. Sure, the DB only has to look at 1 index key to find your result, but it first has to look through all the docs where CaseState==“Created”, and then all of those where IsDelete==false, and then finally it can look at the registration number. A good rule of thumb is to index for specificity first.