How to Read MongoDB Explain Plans

Introduction

The right index on your MongoDB collection can take a query from unusable to near instant. But how do you know which index to add?

Most database management systems provide the ability to look under the hood and understand how a query is executed-- which indexes were used or considered, how many documents or rows were examined, and generally where the time or effort was spent. In MongoDB, that tool is called an "Explain Plan". This post covers how to read MongoDB Explain Plans and how to use them to make your queries faster and more efficient.

How to get a MongoDB Explain Plan

Adding .explain() to a command instructs MongoDB to output a JSON Explain Plan with details about query execution. There are three verbosity levels which control the amount of detail returned:

/*
  The default verbosity is queryPlanner, which reports the execution
  plan selected by the MongoDB query planner.

  The query planner produces this data without executing the query,
  so it's safe and cheap to run but it can't tell you how the query
  actually performs.

  Returns: Query Plan Only
*/
db.collection.find({...}).explain(); // default
db.collection.find({...}).explain("queryPlanner");


/*
  executionStats verbosity also includes stats from actually
  executing the query. e.g., how many documents had to be looked
  at in order to complete the query and how long each step took.

  Returns:
   - Query Plan
   - Winning Plan Execution Stats
*/
db.collection.find({...}).explain("executionStats");


/*
  allPlansExecution verbosity also includes stats from the trial
  executions of candidate plans that informed plan selection.

  Returns:
   - Query Plan
   - Winning Plan Execution Stats
   - Candidate Plan Trial Stats
*/
db.collection.find({...}).explain("allPlansExecution");

Explain Plan JSON Structure

The three Explain Plan verbosity levels are additive-- executionStats includes everything from queryPlanner and adds in the executionStats node, allPlansExecution contains everything from executionStats and adds in the executionStats.allPlansExecution node.

{
  // 1 = Classic engine, 2 = SBE engine
  "explainVersion": "1",
  // How MongoDB planned the query
  "queryPlanner": {
  · "namespace": "app.jobs",
  · // Query after parsing
  · "parsedQuery": {
  · · "status": { "$eq": "queued" }
  · },
  · // Chosen execution strategy (read bottom-to-top)
  · "winningPlan": {
  · · "isCached": false,
  · · "stage": "FETCH",
  · · "inputStage": {
  · · · "stage": "IXSCAN",
  · · · // Which index is used
  · · · "indexName": "status_1_queue_time_1",
  · · · "keyPattern": {
  · · · · "status": 1,
  · · · · "queue_time": 1
  · · · },
  · · · "direction": "forward",
  · · · // Range scanned within the index
  · · · "indexBounds": {
  · · · · "status": [ "[\"queued\", \"queued\"]" ],
  · · · · "queue_time": [ "[MinKey, MaxKey]" ]
  · · · }
  · · }
  · },
  · // Alternative plans MongoDB considered
  · "rejectedPlans": []
  },
  // Hash identifying the canonical query shape
  "queryShapeHash": "F42A3B7D19E08C56...",
  // The command as received by the database
  "command": {
  · "find": "jobs",
  · "filter": { "status": "queued" },
  · "sort": { "queue_time": 1 },
  · "limit": 1,
  · "$db": "app"
  },
  // Database server identity
  "serverInfo": {
  · "host": "mongo-1.example.net",
  · "port": 27017,
  · "version": "8.0.4",
  · "gitVersion": "bc35ab4d4c..."
  },
  // Server configuration
  "serverParameters": {
  · "internalQueryFacetBufferSizeBytes": 104857600,
  · "internalQueryFacetMaxOutputDocSizeBytes": 104857600
  },
  // Whether the query succeeded
  "ok": 1.0
}

Reading Query Plan Stage Trees

The query execution plan is a tree of stages, where each stage performs one operation-- scanning an index, fetching documents, sorting, projecting, limiting-- and passes its output to its parent. The root stage produces the final result returned to the client. Two things about reading these execution plan trees trip people up--

First, data flows from bottom-to-top but the JSON nests top-down. The outermost stage in the JSON is the last stage to run. The mostly deeply nested stages in the JSON are the first to run. For example, when MongoDB shows you a FETCH whose inputStage is an IXSCAN, the IXSCAN runs first. It reads data off the index and feeds its output up into the FETCH stage, which uses those entries to load the corresponding documents.

Second, stage execution time metrics are cumulative, not self-time. Each stage reports executionTimeMillis/executionTimeMillisEstimate, but that number includes the time spent in all of its children. A FETCH stage reporting 250ms with an IXSCAN child reporting 150ms means the FETCH itself only spent 100ms doing its own work. The other 150ms was the IXSCAN running underneath it. To find which stage is actually slow, subtract children from parents as you walk up the tree.

With that in mind, here are the four metrics to monitor when reviewing the stage tree in an explain plan:

• nReturned: how many documents this stage produced.
• docsExamined (on FETCH and COLLSCAN stages): how many documents this stage had to read.
• keysExamined (on IXSCAN stages): how many index entries were walked.
• executionTimeMillis/executionTimeMillisEstimate: cumulative time through this stage.

The most useful diagnostic is the ratio of work-in to work-out. If a stage examined 100,000 documents and returned 1, MongoDB had to review 99,999 documents that it ultimately did not return in the result set. If keysExamined greatly exceeds nReturned on an IXSCAN, the index isn't selective enough for the query. It is narrowing the search, but perhaps not by as much as you would hope. If docsExamined greatly exceeds nReturned on a FETCH, the FETCH is throwing away documents the index couldn't pre-filter.

An excellent plan generally has nReturned ≈ keysExamined ≈ docsExamined. This indicates that approximately every entry the index pointed at was a document you actually wanted, and approximately every document fetched made it into the result. At the very least, you'd like these ratios to be within an order of magnitude.

The scenarios that follow walk through what it looks like when those numbers diverge, and how each indexing change brings them back into alignment.

Index and Query Tuning Example

Now that you know what Explain Plans are and how to get them, let's run through an example query tuning workflow. We'll iteratively tune the indexing and query strategy to transform a poorly performing full collection scan to an optimal query where the project is fully covered by the index.

Scenario: A Scheduler Bottlenecked by its Database

For the purposes of these examples, let's imagine you support an application with a scheduler service that is responsible for finding the next queued job and scheduling it. As the application has grown, you have noticed that the scheduler has not been able to keep up. Application logs indicate that database queries to MongoDB are the bottleneck, and the query retrieving the next queued job is taking close to two seconds.

Here's what an example document looks like in the job collection:

{
  "_id": ObjectId("6745a1b3e4f2c8d901234567"),
  "job_id": "job-28401",
  "status": "queued",
  "queue_time": ISODate("2026-03-29T14:32:00Z"),
  "type": "generate_invoice",
  "payload": {
    "order_id": "ord-91742",
    "currency": "USD",
    "notify": true
  }
}

The troublesome query made by scheduler looks up the oldest queued job:

db.jobs.find({ status: "queued" }).sort({ queue_time: 1 }).limit(1);

The Jobs collection currently has 100,000 total jobs, and currently hovers around 500 queued jobs (0.5%).

Query 1: Collection Scan

When you run this plan in explain mode, MongoDB indicates the query is performing a collection scan (COLLSCAN). Without any index to support query execution, MongoDB must pick up every document in the collection and evaluate if it meets the query conditions. All 100,000 documents must be examined, 99,500 don't match the filter conditions. Then, MongoDB must perform an in-memory sort on the 500 queued jobs to find the 1 job up next.

Query 2: Simple Index

After considering the query filter, you decide to add a supporting index by status.

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

This allows MongoDB to immediately hone in on the 500 queued jobs. Though you could do better, this simple index alone is the biggest win allowing MongoDB to avoid 99,500 document reads. MongoDB fetches the 500 queued documents quickly, but then still has to sort them in memory.

Query 3: Compound Index with Sort Field

Thinking back to what you know about B-Tree indexes, you instead decide to create a compound index on status and queue time.

db.jobs.createIndex({ status: 1, queue_time: 1 });

The first key being status again allows MongoDB to hone in on the right set of 500 queued jobs on the index. However, since the second key is queue time, MongoDB knows that that set of queued jobs appears in the index in sorted order. Because the index is stored pre-sorted, MongoDB only needs to examine the one index key at the edge of the sorted data and fetch and return exactly one document.

Query 4: Covering Index

Believe it or not, you can do even better than a single document fetch. If you modify the query conditions to only project out the fields that the consumer needs, then it's possible to design an index that fully satisfies the query.

Let's say your scheduler only needs the job_id. If you modify the query to only project the job_id

db.jobs
  .find({ status: "queued" }, { _id: 0, job_id: 1 })
  .sort({ queue_time: 1 })
  .limit(1);

And you further modify the index to also include the job_id as a final key

db.jobs.createIndex({ status: 1, queue_time: 1, job_id: 1 });

Then all data required to resolve the query exists in the index itself. MongoDB can read the one index entry and return the result without touching the document collection.

Query 5: Partial Index

The plan from Query 4 already has an efficient query plan-- it examines a single index key and returns the projected fields without needing to fetch the document. However, the index you built contains far more data than is needed to execute the query. Since the query only filters for status: "queued", you can restrict the index to only the documents this query cares about.

db.jobs.createIndex(
  { status: 1, queue_time: 1, job_id: 1 },
  { partialFilterExpression: { status: "queued" } },
);

The execution plan is identical to Query 4, but the index itself is dramatically smaller-- containing entries for just the 500 queued jobs rather than all 100,000 jobs of any status. As jobs are processed, their entries are removed from the index. And as the collection grows over time, the index size stays bounded by the queue depth rather than the collection size. This means the index takes up less space in memory, which can help keep the index hot in the cache, avoiding eviction under memory pressure.

The trade-off is that queries filtering on other statuses (e.g., status: "completed") or that don't filter by status cannot use this index. But that is likely to be an acceptable compromise-- if a query can include completed jobs, then status is no longer a selective filter.

Conclusion

For most cases, the simpler indexes will get you the performance gains needed to support your application. But understanding how your index strategy supports your query strategy and your data modeling strategy unlocks more efficient indexing opportunities. And MongoDB's query Explain Plan is the tool you can use to understand those details on your own database.

JSON is great as structured data, but difficult to read-- which makes an Explain Plan an easy thing to hand to an LLM, and for quick triage you often should. But offloading the reading offloads the learning, and the learning is the point: reading Explain Plans is how you build a mental model of how the database actually executes queries. That mental model is what lets you design the right indexes and data models up front.

MongoDB Paste the Plan

If you found the stage tree diagrams in this blog post instructive, there are many tools available to visualize MongoDB Explain Plans. In fact, MongoDB Compass itself has an Explain Plan viewer much like these diagrams. However, I found that they all fell a bit short. I want a tool that a) operates on the saved JSON b) is web based and c) allows me to share plans privately to co-workers to support decision making during code review.

I couldn't find one, so I built MongoDB Paste the Plan instead.

Paste your Explain Plan into the tool to generate an interactive stage tree, per-stage metrics, and red flags called out visually. It handles some of the finer details, like self vs total time calculations or representing the scoring in an allPlansExecution run of trial plans. It also offers full support for visualizing aggregations or plans executed with the newer SBE engine. All processing is handled client-side in your browser, no plans are saved. It supports sharing plans with others by encoding the plan into a URL hash.

MongoDB Stage Glossary

The stage trees in the example plans above use a handful of stages-- COLLSCAN, IXSCAN, FETCH, SORT, and PROJECTION_COVERED-- all query stages from MongoDB's classic execution engine. But MongoDB has almost 200 different stage types across query planning and execution, with more surely to be added as the product evolves. As a companion to my MongoDB Paste the Plan tool, I've also built a full reference guide of MongoDB execution stages: the MongoDB Stage Glossary. For power users, it even links out to the C++ source file in the MongoDB open-source repository.

Both tools are free and open source, with source available on GitHub at dfrancour/mongodb-paste-the-plan