How to Improve Aggragation Query return time

Working with Data
,
1

Hi All,

I’m using a open source version MongoDB and when I run aggregation Query in the compass it return the results fairly quickly I understand that the query ran only in a small set of data, but when I run the explain function it takes a lot long around 400 seconds to Run the Query.

My Query is the following ::

[{"$lookup":
{"from":"collection_a",
"localField":"user_id",
"foreignField":"user_id",
"as":"user_data"}},
 { "$addFields":{
   "processed_on_date":{
   "$toString":"$processed_on"}
         }
 },
{ "$group":{
    "_id":"$user_id",
     "count":{"$sum":1}
     }
},
{"$match":{"count":{"$eq":2}}
}
]

In this pipeline the lookup from a really small collection and projection of processed_on_date is fast. The main is issue is group user_id and count their total number of apperance. I already have user_id_1 and user_id_-1 indexes there is also user_id_order_id_processed_on composite index.
and these are dynamic queries in my collection that looks like

{
  "order_id": "order_id",
  "user_id": "user_id",
  "tenant_id": "tennat_id",
  "retailer": {
    "id": "some_id",
    "name": "name"
  },
  "geography": {
    "state": "state",
    "city": "city",
    "pincode": "pincode"
  },
  "distributor_id": "distributor_id",
  "total_amount": 1505.13,
  "received_qty": 22,
  "total_ptr": 0,
  "processed_on": {
    "$date": "2024-02-06T04:45:09.116Z"
  },
  "products": [
    {
      "name": "name",
      "id": "product_id",
      "ptr": 0,
      "qty": 3,
      "received_qty": 3,
      "free_qty": 0,
      "amount": 0,
      "brand": {
        "id": "brand_id",
        "name": "brand_id"
      },
      "_id": {
        "$oid": "f"
      }
    },
    {
      "name": "name",
      "id": "product_id",
      "ptr": 0,
      "qty": 3,
      "received_qty": 3,
      "free_qty": 0,
      "amount": 0,
      "brand": {
        "id": "brand_id",
        "name": "brand_id"
      },
      "_id": {
        "$oid": "f"
      }
    },
    {
      "name": "name",
      "id": "product_id",
      "ptr": 0,
      "qty": 3,
      "received_qty": 3,
      "free_qty": 0,
      "amount": 0,
      "brand": {
        "id": "brand_id",
        "name": "brand_id"
      },
      "_id": {
        "$oid": "f"
      }
    },
    {
      "name": "name",
      "id": "product_id",
      "ptr": 0,
      "qty": 3,
      "received_qty": 3,
      "free_qty": 0,
      "amount": 0,
      "brand": {
        "id": "brand_id",
        "name": "brand_id"
      },
      "_id": {
        "$oid": "f"
      }
    },
    {
      "name": "name",
      "id": "product_id",
      "ptr": 0,
      "qty": 3,
      "received_qty": 3,
      "free_qty": 0,
      "amount": 0,
      "brand": {
        "id": "brand_id",
        "name": "brand_id"
      },
      "_id": {
        "$oid": "f"
      }
    }
  ],
  "created_at": {
    "$date": "2024-02-06T04:45:09.143Z"
  },
  "updated_at": {
    "$date": "2024-02-06T04:45:09.143Z"
  },
  "__v": 0
}

and I can also check for sum of total_amount, received_qty, total_ptr at the group stage. How to optimize the collection. Please help.