explain()

explain() reveals exactly how MongoDB will (or did) execute a query or aggregation — which indexes it considered, which one it chose, and how many documents it had to touch along the way. It's the single most important debugging tool for query performance.

Verbosity Modes

Mode	What It Does
queryPlanner (default)	Shows the chosen plan and rejected alternatives, WITHOUT actually running the query
executionStats	Actually RUNS the query and reports real counters — documents examined, keys examined, timing
allPlansExecution	Runs the winning plan fully AND partially runs every rejected candidate plan, for deep plan-comparison debugging

Choosing a verbosity level

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

Warning

executionStats and allPlansExecution actually execute the query against real data. Be careful running them against write-heavy production queries if the query itself would normally trigger side effects — reads are safe, but always understand what you're running explain() against.

Reading the winningPlan

A COLLSCAN — no usable index

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

{
  queryPlanner: {
    winningPlan: {
      stage: 'COLLSCAN',      // full collection scan — every document was examined
      filter: { status: { '$eq': 'shipped' } },
      direction: 'forward'
    }
  },
  executionStats: {
    nReturned: 12904,
    totalKeysExamined: 0,
    totalDocsExamined: 48213,   // scanned EVERY document to find 12904 matches
    executionTimeMillis: 84
  }
}

An IXSCAN — using an index

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

{
  queryPlanner: {
    winningPlan: {
      stage: 'FETCH',                      // fetch full documents from the index-matched keys
      inputStage: {
        stage: 'IXSCAN',                   // index scan — the good sign
        keyPattern: { status: 1 },
        indexName: 'status_1',
        direction: 'forward'
      }
    }
  },
  executionStats: {
    nReturned: 12904,
    totalKeysExamined: 12904,   // examined exactly as many index keys as matches
    totalDocsExamined: 12904,   // fetched exactly as many documents as matches
    executionTimeMillis: 6
  }
}

Common winningPlan Stages

Stage	Meaning
COLLSCAN	Full collection scan — no usable index; scans every document
IXSCAN	Index scan — walks a B-tree index to find candidate documents
FETCH	Retrieves the full document for each index key found by an IXSCAN
SORT	An in-memory sort was required — the chosen index did not already provide the requested order
LIMIT	Caps the number of documents passed upward
SKIP	Discards a number of documents before passing the rest upward
PROJECTION_COVERED	The query was answered ENTIRELY from the index — no FETCH needed at all (a "covered query")

totalKeysExamined vs totalDocsExamined vs nReturned

These three numbers, compared against each other, tell you almost everything about query efficiency.

Ratio	What It Means
totalDocsExamined ≈ nReturned	Efficient — the query examined roughly as many documents as it returned
totalDocsExamined >> nReturned	Inefficient — an index (or a better index) is likely missing; the query is doing a lot of wasted scanning
totalKeysExamined ≈ totalDocsExamined ≈ nReturned	Ideal — the index precisely targeted the matching documents
totalKeysExamined = 0	No index was used at all — likely a COLLSCAN

Tip

A quick rule of thumb: divide totalDocsExamined by nReturned. A ratio near 1 is healthy. A ratio in the hundreds or thousands is a strong signal that a supporting index is missing or the existing index isn't selective enough for this query's filter.

Spotting a Missing Index

Compound query without a supporting index

db.orders.find({ status: "shipped", customerId: 101 }).explain("executionStats")
// If winningPlan.stage is COLLSCAN, or totalDocsExamined is far larger
// than nReturned, a compound index is likely missing:
db.orders.createIndex({ status: 1, customerId: 1 })

Note

See the Compound Index page for how field order in a compound index (the ESR rule — Equality, Sort, Range) affects which queries it can serve efficiently.

Explain for Aggregation

Pipelines can be explained the same way, either with .explain() chained onto aggregate()'s options, or by calling explain() directly on the db.collection handle before .aggregate().

Explaining an aggregation pipeline

db.orders.explain("executionStats").aggregate([
  { $match: { status: "shipped" } },
  { $group: { _id: "$customer", total: { $sum: "$total" } } }
])

The output includes a stages array — one entry per pipeline stage — each showing whether that stage's input came from an index scan and how many documents it processed. Look at the first stage the same way you'd inspect a plain query's explain(): a leading $match should show IXSCAN, not COLLSCAN, whenever an appropriate index exists.

queryPlanner (default) shows the plan without running it; executionStats actually runs it and reports real counters.
COLLSCAN = full scan (bad on large collections); IXSCAN = index used (good).
Compare totalDocsExamined to nReturned — close to 1:1 is healthy, orders of magnitude apart signals a missing or wrong index.
SORT appearing in the plan means the query could NOT use an index to satisfy the requested order.
Aggregation pipelines can be explained the same way — check that a leading $match uses IXSCAN.