Aggregate $lookup and $sort takes so long time

db.users.aggregate([
  {
    "$match": {
      "sourceId": "643d2b71183ef6ad50889c0d"
    }
  },
  {
    "$lookup": {
      "from": "games",
      "let": {
        "gameIds": "$gameIds"
      },
      "pipeline": [
        {
          "$match": {
            "$expr": {
              "$in": [
                "$_id",
                "$$gameIds"
              ]
            }
          }
        },
        {
          "$project": {
            "_id": 1,
            "name": 1,
            "logo": 1
          }
        }
      ],
      "as": "gameIds"
    }
  },
  {
    "$addFields": {
      "lastActivity": {
        "$max": {
          "$map": {
            "input": {
              "$cond": {
                "if": {
                  "$eq": [
                    [

                    ],
                    [

                    ]
                  ]
                },
                "then": "$activities",
                "else": {
                  "$filter": {
                    "input": "$activities",
                    "as": "activity",
                    "cond": {
                      "$in": [
                        "$$activity.activityId",
                        [

                        ]
                      ]
                    }
                  }
                }
              }
            },
            "as": "activity",
            "in": "$$activity.lastActivity"
          }
        }
      },
      "score": {
        "$ifNull": [
          "$score",
          0
        ]
      },
      "badgesCount": {
        "$size": {
          "$cond": {
            "if": {
              "$ne": [
                [

                ],
                [

                ]
              ]
            },
            "then": {
              "$filter": {
                "input": "$badges",
                "as": "badge",
                "cond": {
                  "$in": [
                    "$$badge.activityId",
                    [

                    ]
                  ]
                }
              }
            },
            "else": "$badges"
          }
        }
      },
      "gamesCount": {
        "$size": "$gameIds"
      }
    }
  },
  {
    "$sort": {
      "lastActivity": -1
    }
  },
  {
    "$skip": 0
  },
  {
    "$limit": 100
  },
  {
    "$project": {
      "badgesCount": 1,
      "usernameFormatted": 1,
      "gameIds": 1,
      "score": 1,
      "gamesCount": 1,
      "lastActivity": 1
    }
  }
  ])

Generally, the user collection has 270K documents, and the specific source has 3.7K users. Request takes 33 seconds on the server. And when I run the same query from DataGrip for more than 10 seconds, Any suggestions?

I have indexes for sourceId, asc and desc indexes for scores, and usernameFormatted as I have sorting by them.

Hi @Ani_Davtyan and welcome to MongoDB community forums!!

Based on the query posted, I see that you have been using sort, skip and limit in your aggregation pipeline. As mentioned in the MongoDB documentation make sure to include at least one field in your sort that contains unique values, before passing results to the $skip stage, the field used in the sort stage has unique values.

In saying so, I would be able to help you in more depth, if you could help with a few information regarding the deployment.

  1. A sample document on which the aggregation query has been performed.
  2. The desired result from the aggregation query

Is 33 seconds for the query execution looks desired time for the query to do the processing? If not, could you share the explain output for the query?
Also, the server mentioned above, is this the MongoDb server you are talking about ?

Finally, since we do not have enough expertise on the DataGrip we might not be able to assist you completely with the IDE and would recommend using the JetBrains Community forums for details assistance.

Please feel free to reach out in case of further queries.

Regards
Aasawari