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.