Learn how to use explain() to read query plans, understand collection scans vs index scans, and confirm your indexes are working.

Using explain()

Creating an index does not guarantee MongoDB will use it. MongoDB has a query planner that decides the most efficient way to run each query — sometimes it chooses an index, sometimes it does a collection scan. Sometimes you create an index and it never gets used.

explain() shows you exactly what MongoDB is doing to execute a query — which index it used, how many documents it scanned, and how long it took. It is the most important tool for understanding and fixing query performance.

Basic Usage

Add .explain() to any query to see its execution plan:

db.students.find({ name: "Ali Hassan" }).explain()

You can also pass a verbosity level:

// Default — shows the winning plan
db.students.find({ name: "Ali Hassan" }).explain()

// Shows execution stats — how many docs scanned, time taken
db.students.find({ name: "Ali Hassan" }).explain("executionStats")

// Shows all plans MongoDB considered — including rejected ones
db.students.find({ name: "Ali Hassan" }).explain("allPlansExecution")

For debugging, always use "executionStats" — it gives you the numbers you actually need.

Reading the Output

The explain() output can look intimidating at first. Let's break it down using a real query from our school system.

Without an index — COLLSCAN

Run this before creating any index on name:

db.students.find({ name: "Ali Hassan" }).explain("executionStats")

Output (simplified):

{
  queryPlanner: {
    winningPlan: {
      stage: "COLLSCAN",        // Collection scan — no index used
      direction: "forward"
    }
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 1,               // 1 document returned
    executionTimeMillis: 45,    // took 45 milliseconds
    totalKeysExamined: 0,       // no index keys examined
    totalDocsExamined: 5000     // scanned all 5000 documents
  }
}

Key things to read:

stage: "COLLSCAN" — MongoDB scanned the entire collection
totalDocsExamined: 5000 — checked every document
nReturned: 1 — only returned 1 document
executionTimeMillis: 45 — took 45ms

Scanning 5000 documents to return 1 is very inefficient. This query needs an index.

With an index — IXSCAN

Now create an index and run explain again:

db.students.createIndex({ name: 1 })

db.students.find({ name: "Ali Hassan" }).explain("executionStats")

Output (simplified):

{
  queryPlanner: {
    winningPlan: {
      stage: "FETCH",           // Fetch the document
      inputStage: {
        stage: "IXSCAN",        // Index scan — index was used
        keyPattern: { name: 1 },
        indexName: "name_1",
        direction: "forward"
      }
    }
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 1,               // 1 document returned
    executionTimeMillis: 1,     // took 1 millisecond
    totalKeysExamined: 1,       // examined 1 index key
    totalDocsExamined: 1        // fetched 1 document
  }
}

Key things to read:

stage: "IXSCAN" — MongoDB used an index
indexName: "name_1" — the exact index it used
totalKeysExamined: 1 — looked at 1 index entry
totalDocsExamined: 1 — fetched exactly 1 document
executionTimeMillis: 1 — took 1ms instead of 45ms

Same query. Same result. 45x faster.

The Most Important Fields

When reading explain output, focus on these fields:

Field	What it tells you
`stage`	`COLLSCAN` = no index, `IXSCAN` = index used
`indexName`	Which index MongoDB used
`nReturned`	How many documents the query returned
`totalDocsExamined`	How many documents MongoDB read
`totalKeysExamined`	How many index entries MongoDB checked
`executionTimeMillis`	How long the query took in milliseconds

The ideal query: nReturned equals totalDocsExamined. MongoDB examined exactly the documents it needed — no wasted work.

A problem query: totalDocsExamined is much larger than nReturned. MongoDB read thousands of documents but only returned a few. This usually means a missing or unused index.

winningPlan vs rejectedPlans

When MongoDB receives a query, its query planner considers multiple ways to execute it — different indexes, different approaches. It picks the best one (the winningPlan) and discards the rest (rejectedPlans).

db.students.find({ grade: "10th", enrolled: true }).explain("allPlansExecution")

Output (simplified):

{
  queryPlanner: {
    winningPlan: {
      stage: "FETCH",
      inputStage: {
        stage: "IXSCAN",
        indexName: "grade_1_enrolled_1"
      }
    },
    rejectedPlans: [
      {
        stage: "FETCH",
        inputStage: {
          stage: "IXSCAN",
          indexName: "grade_1"
        }
      }
    ]
  }
}

MongoDB had two indexes it could use — grade_1_enrolled_1 and grade_1. It chose the compound index because it is more specific for this query.

MongoDB caches query plans. After running a query a few times, MongoDB remembers which plan worked best and reuses it. If you add a new index, MongoDB will re-evaluate its cached plans the next time the query runs.

Common Stages You Will See

Stage	Meaning
`COLLSCAN`	Collection scan — no index used
`IXSCAN`	Index scan — an index was used
`FETCH`	Fetch full documents after index lookup
`SORT`	In-memory sort — no index supported the sort
`SORT_KEY`	Sort using an index — fast
`PROJECTION`	Apply projection to filter fields
`LIMIT`	Apply limit
`SKIP`	Apply skip

Seeing SORT in the plan means MongoDB is sorting results in memory — which is slow for large result sets. If you sort frequently by a field, add an index on that field.

Detecting a Slow Query

Here are the warning signs to look for in explain output:

COLLSCAN with a large collection — always bad. Add an index.

stage: "COLLSCAN"
totalDocsExamined: 50000
nReturned: 3

totalDocsExamined much larger than nReturned — index exists but is not selective enough:

totalDocsExamined: 8000
nReturned: 2

In-memory SORT — no index is supporting the sort:

stage: "SORT"
memUsage: 2048000

executionTimeMillis is high — anything over 100ms for a simple query is worth investigating:

executionTimeMillis: 380

explain() on update and delete

explain() works on update and delete operations too — not just find:

// Explain an update
db.students.explain("executionStats").updateOne(
  { name: "Ali Hassan" },
  { $set: { grade: "11th" } }
)

// Explain a delete
db.students.explain("executionStats").deleteOne(
  { name: "Ali Hassan" }
)

When you run explain() on an update or delete, MongoDB does not actually perform the operation. It only shows you the query plan. This is safe to run on production data.

hint() — Force a Specific Index

Sometimes MongoDB's query planner picks a suboptimal index. You can force it to use a specific one with hint():

// Force MongoDB to use the name_1 index
db.students.find({ name: "Ali Hassan" }).hint({ name: 1 })

// Force MongoDB to do a collection scan
db.students.find({ name: "Ali Hassan" }).hint({ $natural: 1 })

Use hint() combined with explain() to compare how different indexes perform on the same query:

// Compare two indexes on the same query
db.students.find({ grade: "10th", enrolled: true })
  .hint({ grade: 1 })
  .explain("executionStats")

db.students.find({ grade: "10th", enrolled: true })
  .hint({ grade: 1, enrolled: 1 })
  .explain("executionStats")

Do not use hint() in production application code unless you have a very specific reason. Let MongoDB's query planner do its job. Use hint() only during debugging and performance testing.

School System — Checking Our Indexes

Let's verify the indexes we created in the previous file are actually being used:

// Check that name index is used
db.students.find({ name: "Ali Hassan" }).explain("executionStats")
// Look for: stage: "IXSCAN", indexName: "name_1"

// Check that compound index is used for grade + enrolled query
db.students.find({ grade: "10th", enrolled: true }).explain("executionStats")
// Look for: stage: "IXSCAN", indexName: "grade_1_enrolled_1"

// Check that subjects multikey index is used
db.students.find({ subjects: "Math" }).explain("executionStats")
// Look for: stage: "IXSCAN", indexName: "subjects_1"

// Check sort performance with enrollmentDate index
db.students.find().sort({ enrollmentDate: -1 }).limit(10).explain("executionStats")
// Look for: stage: "IXSCAN" — not SORT
// If you see SORT, the index is not being used for sorting

Quick Workflow for Fixing a Slow Query

1. Run the query with explain("executionStats")
2. Check the stage — is it COLLSCAN or IXSCAN?
3. Check totalDocsExamined vs nReturned — big gap means problem
4. Check executionTimeMillis — is it too high?
5. If slow — create an index on the filter or sort field
6. Run explain again — confirm stage is now IXSCAN
7. Check executionTimeMillis again — confirm improvement

Make explain("executionStats") a habit during development. Run it on every important query before you ship your app. Finding a missing index in development costs seconds. Finding it in production — after users are complaining about slow pages — costs hours.

Using explain()

On this page