Indexing for Aggregation Pipelines
Indexes benefit an aggregation pipeline the same way they benefit a normal query: a leading $match (or a $sort that follows immediately, before any reshaping stage) can use an index to avoid scanning the entire collection. MongoDB's aggregation optimizer will also attempt to push a later $sort earlier when it's safe to do so, and it can sometimes use an index to satisfy $sort even without an explicit early $match, but relying on this optimization implicitly is riskier than designing the pipeline so filtering stages are explicitly first. For $lookup, indexing the foreignField in the joined collection is just as important as indexing a field used in $match, since the foreign lookup is effectively a query executed once per input document.
Cricket analogy: A stadium's turnstile system that pre-checks tickets against a database index moves fans through far faster than manually checking every name against a paper list, similar to how an index lets $match avoid scanning every document.
Reading explain() Output
Calling .explain('executionStats') on an aggregation pipeline reveals how MongoDB actually executed each stage, including whether a $match used an IXSCAN (index scan) or fell back to a COLLSCAN (full collection scan), how many documents each stage examined versus returned, and the execution time per stage. A pipeline where totalDocsExamined is many times larger than nReturned is a red flag indicating a missing or unused index; conversely, a pipeline whose stages are marked as combined into a single $cursor stage in the explain output shows that MongoDB's optimizer successfully pushed the filter down to the storage layer for efficient execution.
Cricket analogy: A post-match analytics report showing how many deliveries a bowler needed to take a wicket reveals efficiency, similar to explain() showing totalDocsExamined versus nReturned to reveal aggregation efficiency.
db.orders.aggregate([
{ $match: { status: "shipped" } },
{ $group: { _id: "$customerId", total: { $sum: "$amount" } } }
]).explain("executionStats");
// Look for: "stage": "IXSCAN" under the $match's queryPlanner,
// and compare executionStats.totalDocsExamined to nReturned.MongoDB Atlas and Compass provide a visual "Explain Plan" tab that highlights slow stages and index usage without requiring you to parse raw JSON explain output by hand, which is often faster for spotting an unindexed $match during development.
Reducing Pipeline Cost
Beyond indexing, the biggest cost reductions usually come from shrinking the document volume and document size as early as possible: place $match before $lookup or $group, use $project to drop unneeded fields right after a $match rather than carrying full documents through the whole pipeline, and avoid $unwind on large arrays unless the pipeline genuinely needs per-element rows. For pipelines that run repeatedly on largely unchanged data — such as a nightly summary report — writing the results to a separate collection with $merge or $out avoids recomputing the same aggregation on every request, trading some staleness for consistently fast reads.
Cricket analogy: A team that trims its touring squad to only the players likely to feature reduces logistics overhead for the whole tour, similar to trimming documents early with $project before costly stages run.
$out and $merge write pipeline results to a real collection and, in the case of $out, will completely replace an existing collection of the same name — always double-check the target collection name before running a pipeline ending in $out against production data.
- A leading $match or an early $sort can use existing indexes, avoiding a full collection scan (COLLSCAN).
- .explain("executionStats") reveals whether a stage used IXSCAN or COLLSCAN and compares totalDocsExamined to nReturned.
- Indexing foreignField on the joined collection is critical for $lookup performance, since it runs once per input document.
- Reducing document volume and size early — via $match and $project — lowers cost for every later stage.
- $unwind on large arrays should be avoided unless the pipeline genuinely needs per-element documents.
- $merge and $out can precompute and store expensive aggregation results for fast repeated reads.
- $out replaces the entire target collection, so its target name must be verified carefully before running in production.
Practice what you learned
1. What does it mean if explain() shows a $match stage using COLLSCAN?
2. What does a large gap between totalDocsExamined and nReturned in explain() output suggest?
3. Why is indexing foreignField important for $lookup performance?
4. What is a key benefit of writing pipeline results to a collection using $merge or $out?
5. What should you verify before running a pipeline that ends in $out against a production database?
Was this page helpful?
You May Also Like
The Aggregation Pipeline
Learn how MongoDB's aggregation pipeline transforms documents through an ordered sequence of stages to filter, reshape, and summarize data.
$lookup and Joins in MongoDB
Understand how $lookup performs left outer joins across collections in the aggregation pipeline, from simple equality joins to correlated subqueries.
Grouping and Accumulators
Master the $group stage and its accumulator operators to compute sums, averages, counts, and other summaries across groups of documents.