Pipeline Optimization & Output

## Why stage order matters at all A pipeline is executed as a stream of documents flowing from stage to stage. Each stage's cost is roughly (per-document cost) x (documents that reach it). Putting a selective filter at the front therefore reduces the input to *every* downstream stage at once — that is the whole optimization in one sentence. Putting the same filter at the end means every expensive stage did its work on documents that were about to be thrown away. ## Only the leading stages can use an index There is a second, sharper reason, and it is the one that separates a middle answer from a junior one. When a pipeline begins with `$match`, the server hands that predicate to the ordinary query layer: it becomes a query on the collection and is eligible to use an index, exactly like the equivalent `find()`. The same is true of an initial `$sort` (it can be satisfied by an index's ordering) and of `$geoNear`, which must be first and uses a geospatial index. Once any stage has transformed the stream — `$group`, `$unwind`, a `$project` that computes fields, a `$lookup` that attaches an array — the documents flowing downstream are no longer collection documents in index order. A later `$match` is then a linear filter over whatever the stream contains. It still reduces work for stages after it, but nothing about it can touch an index. ## What the optimizer rewrites for you MongoDB's pipeline optimizer applies a set of semantics-preserving rewrites before execution: - **Filter pushdown past reshaping stages.** A `$match` following `$project`, `$unset`, `$addFields`/`$set` is split: the part of the predicate that touches only pre-existing fields is moved ahead of the reshaping stage; the part that touches computed fields stays behind. - **Filter pushdown past `$sort`.** Sorting does not change which documents exist, so a `$match` after a `$sort` can move in front of it — and that is a large win, because it shrinks the sort's input. - **Coalescing adjacent stages.** Two consecutive `$match` stages become one predicate joined with `$and`; consecutive `$limit` stages collapse to the smallest; consecutive `$skip` stages sum; a `$limit` following a `$sort` is folded into the sort so it keeps only the top *n*; a `$limit` after a `$skip` is rewritten so the limit runs first with its amount increased by the skip. - **Dependency (projection) analysis.** The pipeline works out which fields the later stages actually reference and avoids carrying the rest, which reduces the bytes moved per document. ## What it will not do for you The optimizer will not invent a filter, and it will not move one past a stage that produces the field being filtered. The classic case: ```javascript [ { $group: { _id: "$customerId", total: { $sum: "$amount" } } }, { $match: { total: { $gt: 1000 } } } ] ``` `total` does not exist before `$group`, so the `$match` cannot move. That is correct and unavoidable — but it means any filter on the *raw* documents (a date range, a status, a tenant id) is yours to place before the `$group`, and if you leave it after, you have grouped the whole collection for nothing. The same applies to a `$match` on a field that `$lookup` attached: filter the base collection first, and filter the joined side inside the join's own sub-pipeline rather than after it. ## Verify, do not assume `explain()` on an aggregation shows the pipeline *after* these rewrites, so you can read off whether your `$match` moved, whether the leading filter reached an index, and whether a `$limit` was folded into a `$sort`. If the shape in the explain output is the shape you typed, no rewrite happened, and any reordering you wanted has to be written by hand. ## Practical rules Write the most selective, index-backed predicate as stage one. Keep filtering on raw fields ahead of `$group`, `$unwind` and `$lookup`. Reserve post-`$group` `$match` for genuine predicates on aggregates (the equivalent of a HAVING clause). Trim fields early when documents are wide. And remember that a rewritten pipeline never changes results — if reordering a pipeline changed your output, you moved a filter across a stage that changed the field's meaning.