Using explain() to Analyze and Debug Slow MongoDB Queries

Maria
MongoDB
21 Sep, 2026

When a MongoDB query is slow, the temptation is to guess. Maybe it needs an index. Maybe the server is overloaded. Maybe the collection just got big. You add an index, the query seems faster, and you move on without knowing whether the index is even being used, or whether you just caught the server on a quiet moment.

explain() replaces guessing with evidence. It shows you exactly how MongoDB planned and executed a query: which index it picked (if any), how many index keys and documents it had to examine, whether it sorted results in memory, and how long each step took. Once you can read explain output, most slow-query investigations take minutes rather than hours.

This guide covers the three verbosity modes, how to read the plan tree and execution stats, the key ratios that reveal problems, a worked example from collection scan to covered query, explaining aggregations, how the plan cache affects what you see, and the mistakes people make when interpreting results.

Running explain()

In mongosh, you can call explain() on a cursor or on the collection:

// On a cursor
db.orders
  .find({ status: "shipped", customerId: 4812 })
  .sort({ createdAt: -1 })
  .explain("executionStats");

// On the collection, then chain the operation
db.orders
  .explain("executionStats")
  .find({ status: "shipped", customerId: 4812 })
  .sort({ createdAt: -1 });

The collection form also works for aggregate(), count(), distinct(), update(), remove(), and findAndModify(), which is handy for checking the plan of a write without thinking too hard about side effects. Note that with executionStats, explaining an update or delete evaluates the plan but doesn't modify any documents.

From application code, drivers expose the same thing. In Node.js:

const plan = await db
  .collection("orders")
  .find({ status: "shipped", customerId: 4812 })
  .sort({ createdAt: -1 })
  .explain("executionStats");

console.log(plan.executionStats.totalDocsExamined);

The Three Verbosity Modes

Mode	Runs the query?	What you get
`"queryPlanner"` (default)	No	The chosen plan and rejected candidates
`"executionStats"`	Yes, the winning plan	Plan plus actual counts and timings
`"allPlansExecution"`	Yes, all candidates (partially)	Everything above, plus trial stats for rejected plans

queryPlanner is fast and safe on production because it doesn't execute anything. Use it to check which index would be chosen.

executionStats is the mode you'll use most. It runs the query to completion with the winning plan and reports what actually happened. On a very expensive query, that means you pay the full cost, so be thoughtful before running it against a hot production primary.

allPlansExecution adds the trial-period statistics for every candidate plan. Use it when you want to understand why the planner preferred one index over another.

Reading the Plan Tree

The heart of explain output is queryPlanner.winningPlan, a tree of stages. Each stage passes documents or index keys to its parent. You read it from the innermost stage outward.

A healthy plan for an indexed query looks like this:

winningPlan: {
  stage: "FETCH",
  inputStage: {
    stage: "IXSCAN",
    keyPattern: { customerId: 1, status: 1, createdAt: -1 },
    indexName: "customerId_1_status_1_createdAt_-1",
    direction: "forward",
    indexBounds: {
      customerId: ["[4812, 4812]"],
      status: ['["shipped", "shipped"]'],
      createdAt: ["[MaxKey, MinKey]"]
    }
  }
}

The index scan (IXSCAN) finds matching keys, and FETCH loads the corresponding documents. The indexBounds show exactly which range of the index was scanned, which is invaluable for confirming the index is used the way you intended.

In recent MongoDB versions, queries that run on the slot-based execution engine (SBE) may show the classic plan nested under winningPlan.queryPlan, alongside a slotBasedPlan section. The stage names you care about are the same; just look one level deeper.

The Stages You'll See Most

Stage	Meaning	Good or bad?
`COLLSCAN`	Reads every document in the collection	Bad on large collections
`IXSCAN`	Scans a range of an index	Good
`FETCH`	Loads full documents for index matches	Normal, unless it examines far more than it returns
`SORT`	Sorts results in memory	A warning sign; the index doesn't provide the order
`PROJECTION_COVERED`	Returns fields straight from the index	Excellent, no documents fetched
`LIMIT` / `SKIP`	Applies limit or skip	Watch large skips
`OR` / `SORT_MERGE`	Combines several index scans	Fine when each branch is indexed
`EXPRESS` stages	Fast path for simple `_id` lookups in recent versions	Excellent

Reading executionStats

With "executionStats", you get a section like this:

executionStats: {
  executionSuccess: true,
  nReturned: 20,
  executionTimeMillis: 1840,
  totalKeysExamined: 0,
  totalDocsExamined: 2104377,
  executionStages: { stage: "SORT", /* ... */ }
}

Four numbers tell most of the story:

nReturned: documents the query returned.
totalKeysExamined: index entries scanned.
totalDocsExamined: documents loaded from storage.
executionTimeMillis: server-side execution time.

The key insight is the ratio between what was examined and what was returned. In an ideal indexed query, totalKeysExamined, totalDocsExamined, and nReturned are all close to each other. When totalDocsExamined is thousands of times larger than nReturned, MongoDB is doing a lot of work to throw most of it away.

The example above is about as bad as it gets: zero keys examined (no index at all), over two million documents read, and a SORT stage, all to return 20 documents.

A Worked Example

Let's fix that query step by step. The query powers a customer's order history page:

db.orders
  .find({ customerId: 4812, status: "shipped" })
  .sort({ createdAt: -1 })
  .limit(20);

Step 1: The Collection Scan

With no suitable index, explain shows:

winningPlan: {
  stage: "SORT",
  sortPattern: { createdAt: -1 },
  limitAmount: 20,
  inputStage: {
    stage: "COLLSCAN",
    filter: { $and: [{ customerId: { $eq: 4812 } }, { status: { $eq: "shipped" } }] },
    direction: "forward"
  }
}
// executionStats: nReturned 20, totalKeysExamined 0,
// totalDocsExamined 2104377, executionTimeMillis 1840

A COLLSCAN feeding an in-memory SORT. Every order in the collection is read.

Step 2: A Partial Index

A first attempt indexes only the customer:

db.orders.createIndex({ customerId: 1 });

winningPlan: {
  stage: "SORT",
  sortPattern: { createdAt: -1 },
  inputStage: {
    stage: "FETCH",
    filter: { status: { $eq: "shipped" } },
    inputStage: { stage: "IXSCAN", indexName: "customerId_1" }
  }
}
// nReturned 20, totalKeysExamined 3120, totalDocsExamined 3120,
// executionTimeMillis 14

Much better: 3,120 documents instead of two million. But notice two things. The FETCH stage has a filter, meaning it loads each of the customer's orders and discards those that aren't shipped. And the SORT stage is still there, sorting in memory. For a customer with many orders, this still does unnecessary work.

Step 3: Following the ESR Rule

The Equality, Sort, Range (ESR) guideline says to order compound index fields as equality matches first, then sort fields, then range filters. Here, customerId and status are equalities and createdAt is the sort:

db.orders.createIndex({ customerId: 1, status: 1, createdAt: -1 });

winningPlan: {
  stage: "LIMIT",
  limitAmount: 20,
  inputStage: {
    stage: "FETCH",
    inputStage: {
      stage: "IXSCAN",
      indexName: "customerId_1_status_1_createdAt_-1",
      indexBounds: {
        customerId: ["[4812, 4812]"],
        status: ['["shipped", "shipped"]'],
        createdAt: ["[MaxKey, MinKey]"]
      }
    }
  }
}
// nReturned 20, totalKeysExamined 20, totalDocsExamined 20,
// executionTimeMillis 0

This is the ideal shape. No SORT stage (the index delivers documents already ordered by createdAt descending), no filter on FETCH, and keys examined equals documents examined equals documents returned. The query reads exactly 20 index entries and 20 documents.

Step 4: A Covered Query

If the page only shows a few fields, you can go one step further and avoid fetching documents entirely. Project only indexed fields and exclude _id (unless it's in the index):

db.orders
  .find(
    { customerId: 4812, status: "shipped" },
    { _id: 0, createdAt: 1, status: 1 },
  )
  .sort({ createdAt: -1 })
  .limit(20)
  .explain("executionStats");

// winningPlan: LIMIT -> PROJECTION_COVERED -> IXSCAN
// nReturned 20, totalKeysExamined 20, totalDocsExamined 0

totalDocsExamined: 0 is the signature of a covered query. Everything came from the index. For this example it's a nice-to-have; for high-volume queries it can meaningfully reduce disk I/O and cache pressure.

After this exercise, drop the now-redundant customerId_1 index. The compound index can serve any query that filters on customerId alone, since it's a prefix.

Explaining Aggregations

Aggregation pipelines can be explained too:

db.orders.explain("executionStats").aggregate([
  {
    $match: { status: "shipped", createdAt: { $gte: ISODate("2026-09-01") } },
  },
  { $group: { _id: "$customerId", total: { $sum: "$amount" } } },
  { $sort: { total: -1 } },
  { $limit: 10 },
]);

The output looks different from find(). Look for:

The first stage's plan. A $match (and a $sort immediately after it) at the start of the pipeline can use indexes, and its plan appears in a queryPlanner section just like a find(). If you see COLLSCAN there, the index work above applies.
Per-stage stats. With executionStats, later stages report nReturned and executionTimeMillisEstimate, so you can see which stage consumes the time.
Stage order. The optimizer may reorder or merge stages. For example, it moves $match earlier when it can and merges $sort with $limit into a top-k sort. Explain shows the pipeline as it actually ran, which can differ from what you wrote.
Spilling to disk. Blocking stages like $group and $sort have a memory limit per stage. Recent versions allow them to spill to disk by default, and explain can show usedDisk: true. That works but is slow; it's a sign to filter more aggressively before the blocking stage.

The rule of thumb for pipelines: put $match first, make it selective, and make sure it's indexed. Everything after it operates on whatever that first stage lets through.

The Plan Cache and Why Results Change

MongoDB doesn't plan every query from scratch. When several indexes could serve a query, the planner runs a short trial of each candidate, picks the winner, and caches that choice for queries with the same shape (the same filter fields, sort, and projection, regardless of the specific values).

That's why you'll sometimes see this in explain output:

queryPlanner: {
  planCacheShapeHash: "9A1F3B2C",
  planCacheKey: "4E7D0A91",
  winningPlan: { /* ... */ },
  rejectedPlans: [ /* other candidates */ ]
}

(Older versions call the shape hash queryHash.) The rejectedPlans array shows the alternatives the planner considered. Use "allPlansExecution" to see how each performed during the trial.

The plan cache is usually a good thing, but it can cause a confusing problem: a plan that won for one set of values gets reused for values where it's terrible. A classic example is a query where one customer has five orders and another has five million. If you suspect this, you can inspect and clear the cache:

db.orders.getPlanCache().list();
db.orders.getPlanCache().clear();

Clearing is a diagnostic step, not a fix. The durable fix is an index that's good for every value of the shape, which is usually the ESR-ordered compound index.

Forcing an Index with hint()

To compare plans, force a specific index with hint():

db.orders
  .find({ customerId: 4812, status: "shipped" })
  .sort({ createdAt: -1 })
  .hint({ customerId: 1, status: 1, createdAt: -1 })
  .explain("executionStats");

hint() is great for experiments. Be cautious about leaving it in application code: if the index is dropped or renamed, the query fails, and a hinted query can't benefit from a better index added later.

Common Mistakes When Reading explain()

Trusting executionTimeMillis from a single run. The first run may read data from disk; the second finds it in the WiredTiger cache and looks ten times faster. Compare totalKeysExamined and totalDocsExamined, which don't depend on cache warmth. They're the more reliable signal.

Testing on a tiny dataset. On a development database with 500 documents, a collection scan takes 1 ms and looks fine. Explain on production-sized data, or at least on a realistic copy, before drawing conclusions.

Stopping at "it uses an index". An IXSCAN that examines 50,000 keys to return 20 documents is still slow. Always check the examined-to-returned ratios, and look for a SORT stage above the index scan.

Ignoring the filter on FETCH. A filter inside a FETCH stage means documents are loaded and then discarded. That's often the clue that a field belongs in the compound index.

Explaining a different query than the app sends. Field order in the filter doesn't matter, but types do. If your app queries customerId: "4812" (a string) and you explain customerId: 4812 (a number), you're analyzing a query that matches different documents. Copy the exact query from logs or the profiler.

Forgetting the sort and limit. Explaining just the find() filter without the .sort() and .limit() hides the in-memory sort problem entirely.

Using Compass and Atlas

If you prefer a visual view, MongoDB Compass has an Explain Plan tab that renders the plan tree as a diagram with per-stage stats, and it can explain aggregation pipelines built in its pipeline editor. Atlas's Query Profiler and Performance Advisor surface slow query shapes and suggest indexes automatically. They're great for finding candidates, and explain() is how you confirm the fix.

To find which queries to explain in the first place, turn to the profiler: Finding Slow Queries with the MongoDB Database Profiler covers capturing them.

Conclusion

explain() turns query tuning into a repeatable process: run it with "executionStats", read the plan tree from the inside out, compare keys and documents examined against documents returned, and look for COLLSCAN, in-memory SORT, and filters on FETCH. Build compound indexes using the ESR rule, re-run explain, and confirm the numbers line up. When they do, you have proof, not a hunch, that the query is fixed.

Pick the slowest query in your application today, copy it exactly as the app sends it (including sort and limit), and run it with .explain("executionStats"). If totalDocsExamined is more than a few times nReturned, you know exactly where your next index should go.