Optimize $sum of aggregation mongo

Kyle_Rainier · 2025-01-29T07:33:14.802Z

We provide a UI for certain mongo records in a multitenant application. Each list of records is scoped to a user’s account number and whatever filter they enter into the UI. The initial page load is all records (for the account) sorted by time. The first 20 records come back fast, but we also count the total records returned using:

db.event.aggregate(
   [
      { "$match": {"orchData.accountNumber":12345} },
      { "$group": { "_id": "count", "total": { "$sum": 1 } } }
    ]
   )

The group/sort for larger accounts (20mil records) takes ~30 seconds. Is there any way to optimize this?

We have an index on accountNumber, we’ve tried sorting by account and time prior to matching.

steevej · 2025-01-30T13:59:16.794Z

Kyle_Rainier:

we’ve tried sorting … and time prior to matching

Sorting by time makes me think that you might be able to use the Computed Pattern on older data. Also look at

mongodb.com

Store Computed Data - MongoDB Manual v8.0

For example, in accounting it is pointless to recompute every time account totals for months or years that are closed because those totals won’t change anymore. In January 2025 you would not be adding transactions for December 2024 and for the whole 2024 year. So you compute and store the totals at month’s and year’s closing. You probably already do those computations to produce an account statement, the extra steps to store them have less impact that computing anew every time you have to go back.

The performance issue is that most likely that the old transactions documents are not part of the current working set anymore so you have to read them for storage.

steevej · 2025-02-07T13:26:29.937Z

@Kyle_Rainier, I would appreciate a followup on the reply I wrote more than a week ago.

If you found a solution please share.

Thanks

Kyle_Rainier · 2025-02-07T13:59:29.147Z

Hi @steevej, thanks for the response.

Our app is a high volume integration platform, and this particular count is all the events processed in the last 2 months (rolling). Saving a count periodically would give a good approximation, but it could not be recalculated for every new event nor could we capture only net new events as they come across, as far as I can tell. New data comes in and old data drops off the TTL too quickly.