When can a $sort in an aggregation pipeline use an index, and what does a following $limit change?

## Two ways a $sort gets satisfied Any sort in any database is served one of two ways: by reading data that is *already* in the required order, or by collecting everything and ordering it. MongoDB is no different. An index stores keys in sorted order, so walking it produces documents in that order for free, and results can start streaming immediately. Without that, the server performs a blocking sort — it must see the last input document before it can emit the first output document. ## The index-eligible position A `$sort` is only eligible for index ordering while the pipeline is still reading the collection. That means: - `$sort` as the very first stage, or - a leading `$match` followed by `$sort`, where one compound index can serve both the predicate and the ordering. The canonical shape is a filter on an equality field plus a sort on a second field, backed by a compound index whose keys cover both: ```javascript db.orders.createIndex({ status: 1, createdAt: -1 }) db.orders.aggregate([ { $match: { status: "paid" } }, { $sort: { createdAt: -1 } }, { $limit: 20 } ]) ``` Here the server seeks into the index at `status: "paid"` and walks backwards through `createdAt`, delivering twenty documents without ever buffering the matching set. Once any stage changes the stream, that possibility is gone. `$group` produces new documents that exist only in memory; `$unwind` multiplies documents; `$lookup` attaches arrays; a computed `$project` invents fields. A `$sort` after any of them sorts a synthesised stream, and no index describes its order. ## $sort + $limit coalescence The most valuable optimization in this area is the *top-n* rewrite. When a `$limit` follows a `$sort` and no stage in between changes the number of documents, the optimizer folds the limit into the sort. The sort then maintains only *n* candidates as it consumes its input: each incoming document is compared against the current worst candidate and either replaces it or is discarded. The consequence is enormous for memory. A blocking sort of 50 million documents may exceed its budget and spill to disk; the same sort with `{ $limit: 20 }` after it holds twenty documents regardless of input size. This is why "top 10 by score" pipelines usually run fine even when the underlying collection is huge — as long as the `$limit` is actually adjacent enough for the rewrite to apply. Related coalescences follow the same spirit: consecutive `$limit` stages collapse to the smallest, consecutive `$skip` stages sum, and a `$limit` after a `$skip` is rewritten to run first with its amount increased by the skip so that the pair still returns the right window. ## What breaks these optimizations - **A stage between `$sort` and `$limit` that changes the document count.** `$unwind`, `$group` or another `$match` in between prevents the fold, because the limit no longer refers to the same population the sort produced. Stages that only reshape documents, such as `$project`, do not change the count. - **Sorting on a computed field.** `{ $addFields: { score: { $multiply: [...] } } }` followed by `{ $sort: { score: -1 } }` can never use an index — the values did not exist until a moment ago. - **Sorting after a `$group`.** Extremely common and almost always a blocking sort. Usually acceptable, because the number of groups is far smaller than the number of input documents; it becomes a problem exactly when the grouping key has very high cardinality. ## Blocking sorts and memory A blocking `$sort` accumulates state and is therefore subject to the 100 MB per-stage budget. Beyond it the stage either spills to temporary files (permitted by default in MongoDB 6.0 and later, or explicitly with `allowDiskUse: true`) or fails. A pipeline that spills in its sort is telling you either that the sort is in the wrong position, that the input was not filtered enough, or that a `$limit` should have been attached. ## Reading it back `explain()` shows the optimized pipeline, so you can check both facts at once: whether the leading stages produced an index scan providing the order, and whether the limit was absorbed into the sort. If you see a standalone blocking sort with no limit folded in on a large collection, you have found the thing to fix. ## In practice Sort early against an index when you can. When you must sort late, make sure a `$limit` sits directly after the `$sort`, filter hard before the reshaping stages, and carry only the fields the sort and the output need — a blocking sort of narrow documents costs a fraction of the same sort over wide ones.