Help with aggregation pipeline performance

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!

1 Like

Hi @uzgvan ,

It looks like index is only present on timestamp, since you aggregation is in userId , bets and timestamp , I would suggest the following index:

{
userId : 1, 
bet: 1, 
timestamp : -1
}

Thanks
Pavel