Using explain() to Analyze Queries

Every query in MongoDB forces a hidden decision: does the database scan every document in the collection to find matches, or does it jump straight to the right documents using an index? The explain() method exposes that decision. It reports which execution plan MongoDB chose, which alternatives it rejected, and — in its most useful mode — exact numbers on how many documents were examined versus how many were actually needed. Without explain(), you’re tuning indexes by guesswork; with it, you can prove a query is fast for the right reason, not just because your test collection happens to be small.

Overview: How the Query Planner and explain() Work

MongoDB’s query planner decides, for a given query shape (the combination of filter fields, sort order, and projection), how to physically retrieve matching documents. When no index matches the query, the only option is a collection scan, shown in explain output as COLLSCAN: MongoDB reads every document in the collection, in roughly insertion order, and tests each one against the filter. The cost is linear in the size of the collection — perfectly fine for a few thousand documents, and ruinous for tens of millions.

When a usable index exists, the planner can perform an index scan (IXSCAN) instead. An index is a separate, sorted data structure (a B-tree) holding just the indexed field values plus a pointer back to the full document. Because it’s sorted and far smaller than the collection, MongoDB can seek directly to the matching range of keys instead of touching every document, then FETCH the full document for each matching key. This is why explain output for an efficient indexed query typically shows a FETCH stage sitting on top of an IXSCAN stage.

When more than one index could satisfy a query, MongoDB doesn’t guess blindly. It runs a short internal plan race: it begins executing several candidate plans in parallel for a limited number of “works,” then commits to whichever plan produces the required results with the least effort. That winning plan is cached for future queries with the same shape, so the race isn’t repeated every time. explain() is exactly the tool that surfaces this process — which plan won, which ones lost, and how much work each one actually did.

explain() supports three verbosity levels, each doing progressively more work:

• queryPlanner (the default) — shows the winning plan and any rejected plans without running the query at all. Fast, and enough to confirm an index is even being considered.
• executionStats — actually runs the winning plan and reports real numbers: nReturned, totalKeysExamined, totalDocsExamined, and executionTimeMillis. This is the mode you reach for when tuning a real query.
• allPlansExecution — like executionStats, but also partially runs every rejected candidate plan, useful when you suspect the planner chose the wrong index.

explain() isn’t limited to find() — it also works on aggregate(), count(), distinct(), and write operations like updateMany() and deleteMany(), since those also have to locate matching documents before they can act on them.

Syntax

The general form wraps the operation you want to analyze, either by chaining .explain() after a query cursor, or by calling .explain() on the collection first:

// Chain explain() after a find() cursor
db.collection.find(query, projection).explain(verbosity);

// Or call explain() on the collection first -- required for
// operations that don't return a cursor, like updates and aggregations
db.collection.explain(verbosity).updateMany(filter, update);
db.collection.explain(verbosity).aggregate(pipeline);

Verbosity	Executes the query?	Use it for
"queryPlanner" (default)	No	Quick check of which plan would be chosen
"executionStats"	Yes	Real counts: documents examined vs. returned, timing
"allPlansExecution"	Yes (all candidates)	Diagnosing why the planner picked a plan you didn’t expect

Examples

Example 1: Spotting a Collection Scan

Suppose db.orders holds hundreds of thousands of order documents shaped like { _id, customerId, status, orderDate, amount }, with no index on status yet.

db.orders.find({ status: "shipped" }).explain();

Output:

{
  queryPlanner: {
    namespace: 'shop.orders',
    winningPlan: {
      stage: 'COLLSCAN',
      filter: { status: { '$eq': 'shipped' } },
      direction: 'forward'
    },
    rejectedPlans: []
  }
}

The stage: 'COLLSCAN' line is the tell: there’s no index the planner could use, so it’s reading every document in orders and testing each one’s status field. rejectedPlans is empty because no index-based plan was even a candidate.

Example 2: Adding an Index and Confirming It’s Used

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

Output:

status_1

db.orders.find({ status: "shipped" }).explain("executionStats");

Output:

{
  queryPlanner: {
    winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { status: 1 },
        indexName: 'status_1',
        direction: 'forward'
      }
    }
  },
  executionStats: {
    nReturned: 41230,
    executionTimeMillis: 18,
    totalKeysExamined: 41230,
    totalDocsExamined: 41230
  }
}

Now the winning plan is FETCH over IXSCAN: MongoDB walked the status_1 index directly to the 41,230 keys equal to "shipped", then fetched exactly those documents. Notice totalKeysExamined and totalDocsExamined both equal nReturned — every document examined was actually returned. That 1:1 ratio is the signature of an efficient, selective index.

Example 3: Compound Indexes and the ESR Rule

Real queries often filter on one field and sort or range-filter on another. Suppose you also filter by a date range and want the results sorted:

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

db.orders
  .find({ status: "shipped", orderDate: { $gte: new Date("2026-01-01") } })
  .sort({ orderDate: -1 })
  .explain("executionStats");

Output:

{
  queryPlanner: {
    winningPlan: {
      stage: 'FETCH',
      inputStage: {
        stage: 'IXSCAN',
        keyPattern: { status: 1, orderDate: -1 },
        indexName: 'status_1_orderDate_-1'
      }
    }
  },
  executionStats: {
    nReturned: 6402,
    totalKeysExamined: 6402,
    totalDocsExamined: 6402
  }
}

Because the index puts the equality field (status) before the range field (orderDate) — the “ESR” rule: Equality fields first, then Sort fields, then Range fields — MongoDB can use the index for the equality match, the range bound, and the sort all in one pass, with no separate in-memory sort stage. Defined the other way around, { orderDate: -1, status: 1 }, the planner could still use it, but far less efficiently, since it can’t narrow by status before scanning a wide range of dates.

How explain() Works Step by Step

When you run explain("executionStats"), MongoDB performs the following:

1. It parses the query shape and enumerates every index (and the collection scan) that could theoretically satisfy it.
2. It runs a brief trial of each viable candidate plan, tracking how many “works” (index key examinations, document fetches) each one performs.
3. It picks the plan that reached the required results with the least work, and records the others as rejectedPlans.
4. Under executionStats, it then actually runs the winning plan to completion, recording nReturned, totalKeysExamined, totalDocsExamined, and timing.
5. The plan is cached, keyed by query shape, so a structurally identical query (same fields and operators, different values) skips the race next time — until the cache is invalidated, for example after an index is added or dropped, or after enough writes change the collection’s statistics.

For an aggregation pipeline, explain() shows a stage tree per pipeline stage: an early $match that can use an index appears as the same IXSCAN/FETCH pattern wrapped in a $cursor stage, while later stages like $group or $sort that can’t use an index show up as their own stages operating purely in memory over whatever the previous stage streamed to them.

Common Mistakes

Mistake 1: Assuming IXSCAN Means the Query Is Efficient

An index being used is not the same as the index being selective. A boolean field like isPriority only has two possible values, so an index on it alone doesn’t narrow much:

db.orders.createIndex({ isPriority: 1 });
db.orders.find({ isPriority: true }).explain("executionStats");

Output (abridged):

{
  winningPlan: { stage: 'FETCH', inputStage: { stage: 'IXSCAN' } },
  executionStats: {
    nReturned: 210344,
    totalKeysExamined: 210344,
    totalDocsExamined: 210344
  }
}

The plan is IXSCAN, technically “using an index,” but if isPriority is true for 40% of a 500,000-document collection, this still fetches over 200,000 full documents — barely better than a collection scan. The fix is usually a more selective compound index that also narrows on a second, more discriminating field:

db.orders.createIndex({ isPriority: 1, orderDate: -1 });

db.orders
  .find({ isPriority: true, orderDate: { $gte: new Date("2026-07-01") } })
  .explain("executionStats");

Always check totalDocsExamined against nReturned — a ratio close to 1:1 is good; a ratio in the thousands means the index isn’t actually narrowing the search much, regardless of what stage name appears.

Mistake 2: Trusting explain() Results From a Tiny or Unrepresentative Collection

The query planner’s choices, and its plan cache, are sensitive to collection size and data distribution. A query that shows IXSCAN against a 500-document development seed can behave very differently once the collection holds ten million documents, because the cost estimates and cached winning plan were established under very different conditions. Before trusting an explain() result, check roughly how large the real collection is:

db.orders.estimatedDocumentCount();

Output:

512843

Re-run explain("executionStats") against a staging copy that’s close to production scale, not just local seed data, before deciding an index isn’t needed.

Mistake 3: Calling explain() on the Result of a Write Operation

Methods like updateMany() and deleteMany() return a plain write-result object once they’ve already run — that object has no .explain() method:

// Wrong: explain() called on the result of an already-executed write
db.orders
  .updateMany({ status: "pending" }, { $set: { status: "shipped" } })
  .explain();

Call .explain() on the collection first instead, so MongoDB reports on the plan before executing it (or, with executionStats, executes it under measurement):

db.orders
  .explain("executionStats")
  .updateMany({ status: "pending" }, { $set: { status: "shipped" } });

Best Practices

• Reach for executionStats, not just the default queryPlanner mode, whenever you’re judging real performance — the default mode never runs the query and can’t report real document counts.
• Compare totalDocsExamined (and totalKeysExamined) to nReturned on every query you tune; a large gap means the index isn’t selective enough even if it’s technically being used.
• Follow the ESR rule for compound indexes: Equality fields first, then Sort fields, then Range fields, so the index can narrow, order, and range-filter in one pass.
• Watch for a SORT stage in the plan — it means MongoDB is sorting in memory because no index covers the requested sort order, which is expensive and, past a memory limit, will fail outright.
• Re-run explain() against realistic data volumes, not just a small local seed collection, before concluding an index is or isn’t needed.
• Use explain() on write operations (updateMany, deleteMany) too — they must locate matching documents just like find(), and can just as easily fall back to an unindexed collection scan.
• Periodically revisit indexes as query patterns change — an index that was ideal for last year’s queries can go unused and just add write overhead if nothing calls it anymore.

Practice Exercises

1. Create a collection db.reviews with at least a few thousand documents shaped like { productId, rating, createdAt }. Run db.reviews.find({ rating: 5 }).explain("executionStats") before and after creating an index on rating, and compare totalDocsExamined. Expected shape: the “before” run shows stage: 'COLLSCAN'; the “after” run shows FETCH over IXSCAN with a much lower totalDocsExamined.
2. Build a compound index on { productId: 1, createdAt: -1 } for db.reviews, then run a query that filters by productId and sorts by createdAt. Use explain("executionStats") to confirm there is no separate SORT stage in the plan.
3. Take a query you consider “fast” in your own project (or the orders example above) and run it with "allPlansExecution" verbosity. Look at rejectedPlans — was a collection scan ever seriously considered, and how much more work did it cost compared to the winner?

Summary

• explain() shows exactly how MongoDB will (or did) execute a query — no more guessing whether an index is being used.
• COLLSCAN means a full collection scan; IXSCAN (usually under FETCH) means an index was used to narrow the search.
• "queryPlanner" (default) shows the plan without running it; "executionStats" actually runs it and reports real counts; "allPlansExecution" also samples rejected plans.
• An index being used isn’t the same as it being effective — always compare totalDocsExamined to nReturned.
• Compound indexes should generally follow the Equality-Sort-Range (ESR) field order.
• explain() also works on aggregate(), count(), distinct(), and write operations — call .explain() on the collection first for those, not on their result.