MongoDB Aggregations Get Slow the Moment You Sort on an Unindexed Field

The symptom

An activity feed aggregation had been comfortable for a year. We added a sort so the newest entries appeared first, and response times moved from tens of milliseconds to several seconds. Under real traffic the query started failing outright with a memory limit error.

The pipeline itself looked innocent:

db.events.aggregate([
    { $match: { workspaceId, type: 'deploy' } },
    { $sort: { createdAt: -1 } },
    { $limit: 25 }
])

Reading the explain output

explain('executionStats') told the whole story. The $match stage used an index on workspaceId, returned about four hundred thousand documents, and then the $sort stage ran as an in memory sort over all of them just to hand twenty five to the next stage.

A blocking sort has to see every document before it can emit the first one, so the $limit after it saves nothing at all. Past a hundred megabytes of intermediate data, the server stops trying and returns an error instead.

The fix

Give the sort an index it can walk in order. The important detail is the field order in a compound index: equality fields first, then the sort field.

db.events.createIndex({ workspaceId: 1, type: 1, createdAt: -1 })

With that in place the planner walks the index in createdAt order, stops after twenty five matching entries, and never materialises a sort. Execution time dropped to six milliseconds and the number of documents examined went from four hundred thousand to twenty five.

Things worth internalising

• Index field order is not cosmetic. Equality, then sort, then range. Put the range field before the sort field and the sort becomes blocking again.
• The sort direction in a compound index matters when you sort on more than one field. A single field index can be read in either direction, a compound one cannot be read in an arbitrary mix.
• allowDiskUse: true makes the error go away and leaves the nine seconds behind. It is a valid setting for analytics run nightly and the wrong answer for anything a user is waiting on.
• Move $sort as early as possible and always after the most selective $match. A sort that follows $lookup or $unwind can never use an index, because the documents it is sorting no longer exist in any collection.

If a query got slow after a small product change, do not start by rewriting the pipeline. Run explain and find out which stage stopped using an index. It is almost always exactly one stage.