I ran the explain for my pipeline. It’s currently taking a few seconds to run this query. I need to be able to 100x the amount of data, so I’m hoping you can help me figure out what I can do to speed it up?
I am trying to get the top 10 list of users by sum of bets made during a certain time period.
I have indexes on userId and timestamp in the bets table. Not sure if I’m missing any others?
{
"stages": [
{
"$cursor": {
"queryPlanner": {
"plannerVersion": 1,
"namespace": "test.bets",
"indexFilterSet": false,
"parsedQuery": {
"$and": [
{
"timestamp": {
"$lt": 1672012800000
}
},
{
"timestamp": {
"$gte": 1671408000000
}
}
]
},
"queryHash": "81EC0C6F",
"planCacheKey": "AC9017EC",
"winningPlan": {
"stage": "PROJECTION_SIMPLE",
"transformBy": {
"bet": 1,
"timestamp": 1,
"userId": 1,
"_id": 0
},
"inputStage": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"keyPattern": {
"timestamp": 1
},
"indexName": "timestamp_1",
"isMultiKey": false,
"multiKeyPaths": {
"timestamp": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"timestamp": [
"[1671408000000.0, 1672012800000.0)"
]
}
}
}
},
"rejectedPlans": []
},
"executionStats": {
"executionSuccess": true,
"nReturned": 114289,
"executionTimeMillis": 2004,
"totalKeysExamined": 114289,
"totalDocsExamined": 114289,
"executionStages": {
"stage": "PROJECTION_SIMPLE",
"nReturned": 114289,
"executionTimeMillisEstimate": 1249,
"works": 114290,
"advanced": 114289,
"needTime": 0,
"needYield": 0,
"saveState": 147,
"restoreState": 147,
"isEOF": 1,
"transformBy": {
"bet": 1,
"timestamp": 1,
"userId": 1,
"_id": 0
},
"inputStage": {
"stage": "FETCH",
"nReturned": 114289,
"executionTimeMillisEstimate": 1157,
"works": 114290,
"advanced": 114289,
"needTime": 0,
"needYield": 0,
"saveState": 147,
"restoreState": 147,
"isEOF": 1,
"docsExamined": 114289,
"alreadyHasObj": 0,
"inputStage": {
"stage": "IXSCAN",
"nReturned": 114289,
"executionTimeMillisEstimate": 682,
"works": 114290,
"advanced": 114289,
"needTime": 0,
"needYield": 0,
"saveState": 147,
"restoreState": 147,
"isEOF": 1,
"keyPattern": {
"timestamp": 1
},
"indexName": "timestamp_1",
"isMultiKey": false,
"multiKeyPaths": {
"timestamp": []
},
"isUnique": false,
"isSparse": false,
"isPartial": false,
"indexVersion": 2,
"direction": "forward",
"indexBounds": {
"timestamp": [
"[1671408000000.0, 1672012800000.0)"
]
},
"keysExamined": 114289,
"seeks": 1,
"dupsTested": 0,
"dupsDropped": 0
}
}
}
}
},
"nReturned": 114289,
"executionTimeMillisEstimate": 1678
},
{
"$group": {
"_id": "$userId",
"points": {
"$sum": "$bet"
},
"lastBet": {
"$max": "$timestamp"
}
},
"nReturned": 73,
"executionTimeMillisEstimate": 1992
},
{
"$sort": {
"sortKey": {
"points": -1,
"lastBet": 1
},
"limit": 10
},
"nReturned": 10,
"executionTimeMillisEstimate": 1992
}
],
"serverInfo": {
"host": "test",
"port": 27017,
"version": "4.4.17",
"gitVersion": "85de0cc83f4dc64dbbac7fe028a4866228c1b5d1"
},
"ok": 1
}
I would appreciate any pointers.
Cheers and Merry Christmas!