Slow aggregate when $count

Working with Data
,
1

The aggregate below runs in less than 1 second without the final $count stage. But with the $count it takes 562 seconds to run (8 vcpus and 62GB RAM). The count result is 212436.

Any directions for having a faster count?

  {
    "$match": {
      "isDeleted": false,
      "tenant_id": ObjectId( "5ec2a723a73af34fd5964c93" ),
      "$or": [
        {
          "emails": {
            "$exists": true,
            "$not": {
              "$size": 0
            }
          }
        },
        {
          "cellphones": {
            "$exists": true,
            "$not": {
              "$size": 0
            }
          }
        }
      ]
    }
  },  
  { "$lookup": {
    "from": "events",
    "let": { "cId": "$_id" },
    "pipeline": [
      { "$match": 
                 { "$expr":
                    { "$and":
                       [          
                          { "$eq": [ "$contact_id", "$$cId"] }
                        ]                 
                    }}},
                   {
                      "$group":{
                           "_id":"$contact_id",
                           "6059ff5a2aa6a105ae85d7f1": {
                               "$sum": { 
                                   "$cond": [                     
                                   { "$and":
                                           [          
                                              { "$eq": [ "$channel", "email" ] },
                                              { "$eq": [ "$event", "open"] },
                                              { "$eq": [ "$campaign_id", ObjectId( "60648747f78ba3fd5b00e8ba" ) ] }
                                            ]                 
                                        }, 1, 0 ]
                               }
                           }
                      }
                   }                    
    ],
    "as": "events"
  }},
  {
    "$unwind": {
      "path": "$events",
      "preserveNullAndEmptyArrays": true
  }},
  {
     "$addFields": {
     "6059ff5a2aa6a105ae85d7f1": "$events.6059ff5a2aa6a105ae85d7f1"
  }},
  {
     "$project": {
     "events": 0
  }},
  { "$lookup": {
    "from": "events",
    "let": { "cId": "$_id" },
    "pipeline": [
      { "$match": 
                 { "$expr":
                    { "$and":
                       [          
                          { "$eq": [ "$contact_id", "$$cId"] }
                        ]                 
                    }}},
                   {
                      "$group":{
                           "_id":"$contact_id",
                           "6075ecec3319af23fd597b0f": {
                               "$sum": { 
                                   "$cond": [                     
                                   { "$and":
                                           [          
                                              { "$eq": [ "$channel", "email" ] },
                                              { "$eq": [ "$event", "open"] },
                                              { "$eq": [ "$campaign_id", ObjectId( "601c1b8343e5614118d6afa5" ) ] }
                                            ]                 
                                        }, 1, 0 ]
                               }
                           }
                      }
                   }                    
    ],
    "as": "events"
  }},
  {
    "$unwind": {
      "path": "$events",
      "preserveNullAndEmptyArrays": true
  }},
  {
     "$addFields": {
     "6075ecec3319af23fd597b0f": "$events.6075ecec3319af23fd597b0f"
  }},
  {
     "$project": {
     "events": 0
  }},
  { "$lookup": {
    "from": "tagcontacts",
    "let": { "cId": "$_id" },
    "pipeline": [
      { "$match": 
                 { "$expr":
                    { "$and":
                       [          
                          { "$eq": [ "$contact_id", "$$cId"] }
                        ]                 
                    }}},
                   {
                      "$group":{
                           "_id":"$contact_id",
                           "6075ecec3319afd31e597b0c": {
                               "$sum": { 
                                   "$cond": [ { "$eq": [ "$tag", ObjectId( "60478086f4ac576583614c56" ) ] }, 1, 0 ]
                               }
                           }
                      }
                   }                    
    ],
    "as": "tags"
  }},
  {
    "$unwind": {
      "path": "$tags",
      "preserveNullAndEmptyArrays": true
  }},
  {
     "$addFields": {
     "6075ecec3319afd31e597b0c": "$tags.6075ecec3319afd31e597b0c"
  }},
  {
     "$project": {
     "tags": 0
  }},
  { "$lookup": {
    "from": "tagcontacts",
    "let": { "cId": "$_id" },
    "pipeline": [
      { "$match": 
                 { "$expr":
                    { "$and":
                       [          
                          { "$eq": [ "$contact_id", "$$cId"] }
                        ]                 
                    }}},
                   {
                      "$group":{
                           "_id":"$contact_id",
                           "6075ecec3319af462d597b0b": {
                               "$sum": { 
                                   "$cond": [ { "$eq": [ "$tag", ObjectId( "606f1b593b1622a3ec817f80" ) ] }, 1, 0 ]
                               }
                           }
                      }
                   }                    
    ],
    "as": "tags"
  }},
  {
    "$unwind": {
      "path": "$tags",
      "preserveNullAndEmptyArrays": true
  }},
  {
     "$addFields": {
     "6075ecec3319af462d597b0b": "$tags.6075ecec3319af462d597b0b"
  }},
  {
     "$project": {
     "tags": 0
  }},
  {
    "$match": {
      "$and": [
        {
          "$and": [
            {
              "6075ecec3319af23fd597b0f": 0
            },
            {
              "6059ff5a2aa6a105ae85d7f1": 0
            }
          ]
        },
        {
          "$and": [
            {
              "6075ecec3319afd31e597b0c": {
                "$lt": 1
              }
            },
            {
              "6075ecec3319af462d597b0b": {
                "$lt": 1
              }
            }
          ]
        }
      ]
    }
  },
  {
    "$count": "Quantos" 
  }  
], 
{ "allowDiskUse": true })´´´
2

Hi @Admin_MlabsPages_mLa

Without the $count, the aggregation was not executed. It simply returns the cursor for the query, but not execute it. This is true for official drivers and the mongo shell. This is why without $count your query returns in less than a second. It’s because technically the server did nothing.

Once you iterate on the cursor by fetching documents from it (or performing a count), the cursor is then executed for real and that’s why your query takes a longer time to return.

As to why it takes that much time to return, it’s worth noting that your pipeline has 18 stages, with four $lookup and a $match stages with $or and $exists: true clauses that may not be able to use any index.

Best regards,
Kevin

1 Like