Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 70 ยท Aggregation pipeline

Aggregation Performance and Explain

Make MongoDB aggregation pipelines fast: put $match first, use indexes, respect the 100 MB stage limit, read explain output and learn what the optimizer does.

A pipeline that works on 500 test documents can crawl on 50 million. Aggregation performance mostly comes down to one idea: do as little work as possible on as few documents as possible, as early as possible. Every stage receives the output of the stage before it, so a cheap filter at the start saves work in every stage that follows.

This lesson gives you the practical rules: put $match and $limit first, let indexes serve the front of the pipeline, understand the per-stage memory limit, read explain output, and know which rewrites MongoDB's optimizer makes for you. The shell blocks are real MongoDB and read-only; the TryIt is a stand-in that counts how many documents flow through each stage, so you can see the rules in numbers. Indexes themselves are the subject of the next chapter, starting with How Indexes Work.

Five rules of thumb

Filter early $match goes first.
Trim early Drop fields and documents you will not use.
Use indexes Only the leading stages can.
Mind memory Sort and group have a limit.
1 / 2

Stage order decides the work

Compare these two pipelines that answer the same question, "total spent by customers in Oslo on shipped orders":

JavaScript
// Slow shape: group everything, then filter
db.orders.aggregate([
  { $group: { _id: "$customerId", spent: { $sum: "$total" }, city: { $first: "$city" } } },
  { $match: { city: "Oslo" } }
])

// Fast shape: filter first, then group only what matters
db.orders.aggregate([
  { $match: { city: "Oslo", status: "shipped" } },
  { $group: { _id: "$customerId", spent: { $sum: "$total" } } }
])

The second version feeds far fewer documents into $group and is also logically more precise. Of course you cannot always move a $match first: a filter on a computed field (the sum, a rank, a joined value) has to wait for the stage that creates it. The trick is to split the filter. Conditions on original fields go first, conditions on computed fields stay where they are.

The same thinking applies to other stages:

  • $limit early when you can: after a $sort it lets the server keep only the top few documents.
  • $project or $unset early to drop large fields (big arrays, text bodies) you will not use. Narrow documents mean less memory in later $group and $sort stages.
  • $unwind late, after $match, because it multiplies documents.
  • $lookup late, after the stream has been filtered and limited, as explained in Joining with $lookup.

The stand-in below runs the two shapes over 5,000 generated orders and reports how many documents each stage receives.

JS Counting the work: filter first or group first

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Look at what each $group receives: all 5,000 documents in the slow shape, but only the 250 that survive the $match in the fast one. (The group counts differ because the fast shape also filters on status, a more precise question, and the slow shape's city comes from the first order of each group, a subtle bug that a leading $match avoids.)

Indexes only help the front

MongoDB can use an index for the stages at the start of a pipeline: $match (and a $sort that follows it) can use an index, and $group stages can use one in a few special cases such as $first after a matching $sort. Once a stage that reshapes documents runs ($group, $project with computed fields, $unwind, $lookup), everything after it works on in-memory results with no indexes.

That is the strongest argument for putting $match and $sort first and keeping them on indexed fields. A leading { $match: { status: "shipped", date: { $gte: ... } } } backed by a compound index touches only the matching entries instead of scanning the collection (Designing Compound Indexes explains how to design one). A $match placed after $project or $addFields that creates the field it filters on cannot use the collection's indexes at all.

Memory limits and allowDiskUse

Blocking stages such as $group, $sort (without an index) and $bucket must hold their data while they work. Each is limited to 100 MB of RAM. Over the limit, one of two things happens depending on version and settings: the stage spills to temporary files on disk (slower but it finishes), or it fails with an error.

JavaScript
db.events.aggregate(pipeline, { allowDiskUse: true })

On older versions you had to pass allowDiskUse: true yourself. MongoDB 6.0 and later spill to disk by default unless an administrator has switched that off. Spilling is a safety net, not a plan: if a pipeline regularly goes to disk, shrink it instead. Filter earlier, project away large fields, use a $sort that an index can serve, and consider $group with fewer distinct keys. Remember that the total size of a single result document is also capped at 16 MB, which matters for $push and $addToSet accumulators that collect everything into one array.

Reading explain on a pipeline

explain shows the plan without or with execution statistics:

JavaScript
db.orders.explain("executionStats").aggregate([
  { $match: { city: "Oslo", status: "shipped" } },
  { $group: { _id: "$customerId", spent: { $sum: "$total" } } }
])

You will see a tree of stages. The numbers worth reading first (their exact layout varies by version and server topology) are:

  • The first stage: COLLSCAN means a full collection scan; IXSCAN means an index was used (inside a FETCH stage if documents also had to be loaded).
  • totalDocsExamined against nReturned: examining 2,000,000 documents to return 40 is a red flag.
  • totalKeysExamined: the number of index entries scanned. Close to nReturned is good.
  • executionTimeMillis: where the time goes.

explain() and the Profiler goes deep on all of this; for now, remember to run it with executionStats on a realistic amount of data, because plans on ten documents prove nothing.

What the optimizer does for you

MongoDB rewrites pipelines before running them, so some obvious mistakes are fixed automatically:

  • A $match after a $sort is moved before it (filter, then sort fewer documents).
  • A $match is moved before a $project or $addFields when it only touches fields those stages do not change.
  • A $sort followed by $limit is merged into a "top k" sort that only keeps the best few documents in memory.
  • Adjacent $match stages are combined, as are adjacent $limit, $skip and $project stages in many cases.
  • Fields the pipeline never uses may be dropped early, so documents carry less data.

Do not rely on this. The optimizer cannot reorder across stages whose meaning depends on order, and it cannot guess your intent. Write the pipeline the way you want it to run, then confirm with explain, which shows the optimized pipeline in its output.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is it usually faster to put $match at the start of a pipeline?

    A leading $match reduces the work of all following stages, and only the front of a pipeline can use the collection's indexes.
  2. 2. You need to filter on a total computed in a $group stage. Where must that $match go?

    A condition on a computed field has to wait for the stage that creates it. Conditions on original fields can still be moved to the front.
  3. 3. What does allowDiskUse: true change?

    The per-stage memory limit is 100 MB. Spilling to disk lets the stage finish, at the cost of speed. The 16 MB document limit is unaffected.
  4. 4. In explain output, which sign suggests a query is doing far more work than needed?

    Examining millions of documents to return a handful means the filter is not selective for the access path in use, typically a missing or unsuitable index.

Summary

  • Work per stage equals documents received, so filter, trim and limit as early as possible; $unwind and $lookup belong late.
  • Only the leading stages ($match, then $sort) can use indexes; after $group, $project with computed fields, $unwind or $lookup, results are in memory.
  • Blocking stages are capped at 100 MB each; allowDiskUse (default in 6.0+) spills to disk, but regularly spilling is a design smell.
  • explain("executionStats") shows COLLSCAN or IXSCAN and the ratio of documents examined to documents returned.
  • The optimizer reorders and merges some stages, but write pipelines in the order you intend and verify.

Next, learn what makes lookups fast in How Indexes Work.