Optimize query with multiple condition in group

Hi,

I’ve a collection with 760K documents.

The query takes up to 5 seconds to aggregate data. It’s very slow.
So I’ve created some indexes to increase performance but it’s always slow :

Indexes :

Result with explain execution stats :

{
  "explainVersion": "2",
  "stages": [
    {
      "$cursor": {
        "queryPlanner": {
          "namespace": "Tracking.eventsraw",
          "indexFilterSet": false,
          "parsedQuery": {
            "$and": [
              {
                "CarrierEventType": {
                  "$exists": true
                }
              },
              { "IdCompany": { "$exists": true } }
            ]
          },
          "queryHash": "3960270F",
          "planCacheKey": "28BEA210",
          "maxIndexedOrSolutionsReached": false,
          "maxIndexedAndSolutionsReached": false,
          "maxScansToExplodeReached": false,
          "winningPlan": {
            "queryPlan": {
              "stage": "GROUP",
              "planNodeId": 3,
              "inputStage": {
                "stage": "FETCH",
                "planNodeId": 2,
                "filter": {
                  "$and": [
                    {
                      "IdCompany": {
                        "$exists": true
                      }
                    },
                    {
                      "CarrierEventType": {
                        "$exists": true
                      }
                    }
                  ]
                },
                "inputStage": {
                  "stage": "IXSCAN",
                  "planNodeId": 1,
                  "keyPattern": {
                    "IdCompany": 1,
                    "CarrierEventType": 1
                  },
                  "indexName": "IdCompany_1_CarrierEventType_1",
                  "isMultiKey": false,
                  "multiKeyPaths": {
                    "IdCompany": [],
                    "CarrierEventType": []
                  },
                  "isUnique": false,
                  "isSparse": false,
                  "isPartial": false,
                  "indexVersion": 2,
                  "direction": "forward",
                  "indexBounds": {
                    "IdCompany": [
                      "[MinKey, MaxKey]"
                    ],
                    "CarrierEventType": [
                      "[MinKey, MaxKey]"
                    ]
                  }
                }
              }
            },
            "slotBasedPlan": {
              "slots": "$$RESULT=s40 env: { s1 = TimeZoneDatabase(Antarctica/Davis...Indian/Mauritius) (timeZoneDB), s2 = Nothing (SEARCH_META), s3 = Timestamp(1703087130, 1) (CLUSTER_TIME), s4 = 1703087131279 (NOW) }",
              "stages": "[3] mkobj s40 [_id = s35, published = s36, publishedFailed = s37, completed = s38, created = s39] true false \n[3] project [s35 = newObj (\"carriername\", s15, \"companyname\", s16), s36 = doubleDoubleSumFinalize (s21), s37 = doubleDoubleSumFinalize (s25), s38 = doubleDoubleSumFinalize (s29), s39 = doubleDoubleSumFinalize (s34)] \n[3] group [s15, s16] [s21 = aggDoubleDoubleSum (s20), s25 = aggDoubleDoubleSum (s24), s29 = aggDoubleDoubleSum (s28), s34 = aggDoubleDoubleSum (s33)] \n[3] nlj [s13, s15, s16, s17, s20, s24, s28, s30] [s13, s15, s16, s17, s20, s24, s28, s30] \n    left \n        [3] project [s30 = getField (s13, \"_id\")] \n        [3] nlj [s13, s15, s16, s17, s20, s24] [s13, s15, s16, s17, s20, s24] \n            left \n                [3] nlj [s13, s15, s16, s17, s20] [s13, s15, s16, s17, s20] \n                    left \n                        [3] nlj [s13, s15, s16, s17] [s13, s15, s16, s17] \n                            left \n                                [3] project [s17 = getField (s13, \"State\")] \n                                [3] project [s16 = getField (s13, \"IdCompany\")] \n                                [3] project [s15 = getField (s13, \"CarrierEventType\")] \n                                [2] filter {applyClassicMatcher (ClassicMatcher({ $and: [ { IdCompany: { $exists: true } }, { CarrierEventType: { $exists: true } } ] }), s13)} \n                                [2] nlj [] [s9, s5, s6, s7, s8] \n                                    left \n                                        [1] nlj [s6, s8] [s10, s11] \n                                            left \n                                                [1] project [s6 = \"IdCompany_1_CarrierEventType_1\", s8 = {\"IdCompany\" : 1, \"CarrierEventType\" : 1}, s10 = KS(0A0A0104), s11 = KS(F0F0FE04)] \n                                                [1] limit 1 \n                                                [1] coscan \n                                            right \n                                                [1] project [s5 = s12] \n                                                [1] ixseek s10 s11 s7 s9 s12 [] @\"6616dab0-d2b1-443b-a3c7-62b4494fb99f\" @\"IdCompany_1_CarrierEventType_1\" true \n                                            \n                                        \n                                    right \n                                        [2] limit 1 \n                                        [2] seek s9 s13 s14 s5 s6 s7 s8 [] @\"6616dab0-d2b1-443b-a3c7-62b4494fb99f\" true false \n                                    \n                                \n                            right \n                                [3] limit 1 \n                                [3] union [s20] [\n                                    [s18] [3] nlj [] [] \n                                        left \n                                            [3] filter {let [l2.0 = let [l1.0 = s17, l1.1 = \"Published\"] fillEmpty (l1.0 <=> l1.1 == 0, exists (l1.0) && typeMatch (l1.0, -65) == exists (l1.1) && typeMatch (l1.1, -65))] exists (l2.0) && ! typeMatch (l2.0, 1088) && l2.0 <=> false != 0 && l2.0 <=> 0 != 0} \n                                            [3] limit 1 \n                                            [3] coscan \n                                        right \n                                            [3] project [s18 = 1] \n                                            [3] limit 1 \n                                            [3] coscan \n                                        \n                                    , \n                                    [s19] [3] project [s19 = 0] \n                                    [3] limit 1 \n                                    [3] coscan \n                               ] \n                            \n                        \n                    right \n                        [3] limit 1 \n                        [3] union [s24] [\n                            [s22] [3] nlj [] [] \n                                left \n                                    [3] filter {let [l4.0 = let [l3.0 = s17, l3.1 = \"PublishedFailed\"] fillEmpty (l3.0 <=> l3.1 == 0, exists (l3.0) && typeMatch (l3.0, -65) == exists (l3.1) && typeMatch (l3.1, -65))] exists (l4.0) && ! typeMatch (l4.0, 1088) && l4.0 <=> false != 0 && l4.0 <=> 0 != 0} \n                                    [3] limit 1 \n                                    [3] coscan \n                                right \n                                    [3] project [s22 = 1] \n                                    [3] limit 1 \n                                    [3] coscan \n                                \n                            , \n                            [s23] [3] project [s23 = 0] \n                            [3] limit 1 \n                            [3] coscan \n                       ] \n                    \n                \n            right \n                [3] limit 1 \n                [3] union [s28] [\n                    [s26] [3] nlj [] [] \n                        left \n                            [3] filter {let [l6.0 = let [l5.0 = s17, l5.1 = \"Completed\"] fillEmpty (l5.0 <=> l5.1 == 0, exists (l5.0) && typeMatch (l5.0, -65) == exists (l5.1) && typeMatch (l5.1, -65))] exists (l6.0) && ! typeMatch (l6.0, 1088) && l6.0 <=> false != 0 && l6.0 <=> 0 != 0} \n                            [3] limit 1 \n                            [3] coscan \n                        right \n                            [3] project [s26 = 1] \n                            [3] limit 1 \n                            [3] coscan \n                        \n                    , \n                    [s27] [3] project [s27 = 0] \n                    [3] limit 1 \n                    [3] coscan \n               ] \n            \n        \n    right \n        [3] limit 1 \n        [3] union [s33] [\n            [s31] [3] nlj [] [] \n                left \n                    [3] filter {let [l8.0 = let [l7.0 = s30, l7.1 = ObjectId(\"65822e800000000000000000\")] fillEmpty (l7.0 <=> l7.1 >= 0, exists (l7.0) && typeMatch (l7.0, -65) >= exists (l7.1) && typeMatch (l7.1, -65))] exists (l8.0) && ! typeMatch (l8.0, 1088) && l8.0 <=> false != 0 && l8.0 <=> 0 != 0} \n                    [3] limit 1 \n                    [3] coscan \n                right \n                    [3] project [s31 = 1] \n                    [3] limit 1 \n                    [3] coscan \n                \n            , \n            [s32] [3] project [s32 = 0] \n            [3] limit 1 \n            [3] coscan \n       ] \n    \n"
            }
          },
          "rejectedPlans": [
            {
              "queryPlan": {
                "stage": "GROUP",
                "planNodeId": 3,
                "inputStage": {
                  "stage": "FETCH",
                  "planNodeId": 2,
                  "filter": {
                    "$and": [
                      {
                        "IdCompany": {
                          "$exists": true
                        }
                      },
                      {
                        "CarrierEventType": {
                          "$exists": true
                        }
                      }
                    ]
                  },
                  "inputStage": {
                    "stage": "IXSCAN",
                    "planNodeId": 1,
                    "keyPattern": {
                      "IdCompany": 1,
                      "CarrierEventType": 1,
                      "State": 1
                    },
                    "indexName": "IdCompany_1_CarrierEventType_1_State_1",
                    "isMultiKey": false,
                    "multiKeyPaths": {
                      "IdCompany": [],
                      "CarrierEventType": [],
                      "State": []
                    },
                    "isUnique": false,
                    "isSparse": false,
                    "isPartial": false,
                    "indexVersion": 2,
                    "direction": "forward",
                    "indexBounds": {
                      "IdCompany": [
                        "[MinKey, MaxKey]"
                      ],
                      "CarrierEventType": [
                        "[MinKey, MaxKey]"
                      ],
                      "State": [
                        "[MinKey, MaxKey]"
                      ]
                    }
                  }
                }
              },
              "slotBasedPlan": {
                "slots": "$$RESULT=s40 env: { s1 = TimeZoneDatabase(Antarctica/Davis...Indian/Mauritius) (timeZoneDB), s2 = Nothing (SEARCH_META), s3 = Timestamp(1703087130, 1) (CLUSTER_TIME), s4 = 1703087131279 (NOW) }",
                "stages": "[3] mkobj s40 [_id = s35, published = s36, publishedFailed = s37, completed = s38, created = s39] true false \n[3] project [s35 = newObj (\"carriername\", s15, \"companyname\", s16), s36 = doubleDoubleSumFinalize (s21), s37 = doubleDoubleSumFinalize (s25), s38 = doubleDoubleSumFinalize (s29), s39 = doubleDoubleSumFinalize (s34)] \n[3] group [s15, s16] [s21 = aggDoubleDoubleSum (s20), s25 = aggDoubleDoubleSum (s24), s29 = aggDoubleDoubleSum (s28), s34 = aggDoubleDoubleSum (s33)] \n[3] nlj [s13, s15, s16, s17, s20, s24, s28, s30] [s13, s15, s16, s17, s20, s24, s28, s30] \n    left \n        [3] project [s30 = getField (s13, \"_id\")] \n        [3] nlj [s13, s15, s16, s17, s20, s24] [s13, s15, s16, s17, s20, s24] \n            left \n                [3] nlj [s13, s15, s16, s17, s20] [s13, s15, s16, s17, s20] \n                    left \n                        [3] nlj [s13, s15, s16, s17] [s13, s15, s16, s17] \n                            left \n                                [3] project [s17 = getField (s13, \"State\")] \n                                [3] project [s16 = getField (s13, \"IdCompany\")] \n                                [3] project [s15 = getField (s13, \"CarrierEventType\")] \n                                [2] filter {applyClassicMatcher (ClassicMatcher({ $and: [ { IdCompany: { $exists: true } }, { CarrierEventType: { $exists: true } } ] }), s13)} \n                                [2] nlj [] [s9, s5, s6, s7, s8] \n                                    left \n                                        [1] nlj [s6, s8] [s10, s11] \n                                            left \n                                                [1] project [s6 = \"IdCompany_1_CarrierEventType_1_State_1\", s8 = {\"IdCompany\" : 1, \"CarrierEventType\" : 1, \"State\" : 1}, s10 = KS(0A0A0A0104), s11 = KS(F0F0F0FE04)] \n                                                [1] limit 1 \n                                                [1] coscan \n                                            right \n                                                [1] project [s5 = s12] \n                                                [1] ixseek s10 s11 s7 s9 s12 [] @\"6616dab0-d2b1-443b-a3c7-62b4494fb99f\" @\"IdCompany_1_CarrierEventType_1_State_1\" true \n                                            \n                                        \n                                    right \n                                        [2] limit 1 \n                                        [2] seek s9 s13 s14 s5 s6 s7 s8 [] @\"6616dab0-d2b1-443b-a3c7-62b4494fb99f\" true false \n                                    \n                                \n                            right \n                                [3] limit 1 \n                                [3] union [s20] [\n                                    [s18] [3] nlj [] [] \n                                        left \n                                            [3] filter {let [l2.0 = let [l1.0 = s17, l1.1 = \"Published\"] fillEmpty (l1.0 <=> l1.1 == 0, exists (l1.0) && typeMatch (l1.0, -65) == exists (l1.1) && typeMatch (l1.1, -65))] exists (l2.0) && ! typeMatch (l2.0, 1088) && l2.0 <=> false != 0 && l2.0 <=> 0 != 0} \n                                            [3] limit 1 \n                                            [3] coscan \n                                        right \n                                            [3] project [s18 = 1] \n                                            [3] limit 1 \n                                            [3] coscan \n                                        \n                                    , \n                                    [s19] [3] project [s19 = 0] \n                                    [3] limit 1 \n                                    [3] coscan \n                               ] \n                            \n                        \n                    right \n                        [3] limit 1 \n                        [3] union [s24] [\n                            [s22] [3] nlj [] [] \n                                left \n                                    [3] filter {let [l4.0 = let [l3.0 = s17, l3.1 = \"PublishedFailed\"] fillEmpty (l3.0 <=> l3.1 == 0, exists (l3.0) && typeMatch (l3.0, -65) == exists (l3.1) && typeMatch (l3.1, -65))] exists (l4.0) && ! typeMatch (l4.0, 1088) && l4.0 <=> false != 0 && l4.0 <=> 0 != 0} \n                                    [3] limit 1 \n                                    [3] coscan \n                                right \n                                    [3] project [s22 = 1] \n                                    [3] limit 1 \n                                    [3] coscan \n                                \n                            , \n                            [s23] [3] project [s23 = 0] \n                            [3] limit 1 \n                            [3] coscan \n                       ] \n                    \n                \n            right \n                [3] limit 1 \n                [3] union [s28] [\n                    [s26] [3] nlj [] [] \n                        left \n                            [3] filter {let [l6.0 = let [l5.0 = s17, l5.1 = \"Completed\"] fillEmpty (l5.0 <=> l5.1 == 0, exists (l5.0) && typeMatch (l5.0, -65) == exists (l5.1) && typeMatch (l5.1, -65))] exists (l6.0) && ! typeMatch (l6.0, 1088) && l6.0 <=> false != 0 && l6.0 <=> 0 != 0} \n                            [3] limit 1 \n                            [3] coscan \n                        right \n                            [3] project [s26 = 1] \n                            [3] limit 1 \n                            [3] coscan \n                        \n                    , \n                    [s27] [3] project [s27 = 0] \n                    [3] limit 1 \n                    [3] coscan \n               ] \n            \n        \n    right \n        [3] limit 1 \n        [3] union [s33] [\n            [s31] [3] nlj [] [] \n                left \n                    [3] filter {let [l8.0 = let [l7.0 = s30, l7.1 = ObjectId(\"65822e800000000000000000\")] fillEmpty (l7.0 <=> l7.1 >= 0, exists (l7.0) && typeMatch (l7.0, -65) >= exists (l7.1) && typeMatch (l7.1, -65))] exists (l8.0) && ! typeMatch (l8.0, 1088) && l8.0 <=> false != 0 && l8.0 <=> 0 != 0} \n                    [3] limit 1 \n                    [3] coscan \n                right \n                    [3] project [s31 = 1] \n                    [3] limit 1 \n                    [3] coscan \n                \n            , \n            [s32] [3] project [s32 = 0] \n            [3] limit 1 \n            [3] coscan \n       ] \n    \n"
              }
            }
          ]
        },
        "executionStats": {
          "executionSuccess": true,
          "nReturned": 5,
          "executionTimeMillis": 5354,
          "totalKeysExamined": 766747,
          "totalDocsExamined": 766747,
          "executionStages": {
            "stage": "mkobj",
            "planNodeId": 3,
            "nReturned": 5,
            "executionTimeMillisEstimate": 5320,
            "opens": 1,
            "closes": 1,
            "saveState": 767,
            "restoreState": 767,
            "isEOF": 1,
            "objSlot": 40,
            "fields": [],
            "projectFields": [
              "_id",
              "published",
              "publishedFailed",
              "completed",
              "created"
            ],
db.getCollection('eventsraw').aggregate(
  [
    {
      $match: {
        CarrierEventType: { $exists: true },
        IdCompany: { $exists: true }
      }
    },
    {
      $group: {
        _id: {
          carriername: '$CarrierEventType',
          companyname: '$IdCompany'
        },
        published: {
          $sum: {
            $cond: [
              { $eq: ['$State', 'Published'] },
              1,
              0
            ]
          }
        },
        publishedFailed: {
          $sum: {
            $cond: [
              {
                $eq: ['$State', 'PublishedFailed']
              },
              1,
              0
            ]
          }
        },
        completed: {
          $sum: {
            $cond: [
              { $eq: ['$State', 'Completed'] },
              1,
              0
            ]
          }
        },
        created: {
          $sum: {
            $cond: [
              {
                $gte: [
                  '$_id',
                      ObjectId(Math.floor(new Date().setUTCHours(0, 0, 0, 0) / 1000).toString(16) + "0000000000000000")
                  )
                ]
              },
              1,
              0
            ]
          }
        }
      }
    },
    {
      $project: {
        _id: 0,
        carriername: '$_id.carriername',
        companyname: '$_id.companyname',
        published: 1,
        publishedFailed: 1,
        completed: 1,
        created: 1,
        ischange: 'false'
      }
    }
  ]
);

How selective is this $match? It looks like every document will have these two fields, no? Why are you trying to force the use of an index then, I suspect collection scan will actually be faster. If not all your documents have these two fields I would suggest changing the query to

    CarrierEventType: { $ne: null },
    IdCompany: { $ne: null }

and maybe creating an index on {CarrierEventType:1, IdCompany:1, State:1, Published:1, PublishedFailed:1, Completed:1} since that’s what you would need to have a fully covered index aggregation. However, if this is aggregating across your entire collection you’re probably better off with a collection scan.

Asya

1 Like