beginnerintermediateadvanced

MongoDB · explain() and Performance Analysis

explain() and Performance Analysis

Choosing verbosity, reading the key numbers, and diagnosing real production traffic with the profiler

Match verbosity to the questionallPlansExecution costs the most
Ratio near 1 = well-alignednReturned vs. keys/docs examined
Visible in the slow-query logtriage without running explain() first
Before/after, not "feels faster"capture both explain() states

1.Choosing the right explain() verbosity

explain("queryPlanner") — the default — shows the winning plan's shape with no execution. explain("executionStats") actually runs the query and adds real numbers for the winner. explain("allPlansExecution") adds those same real numbers for every candidate, useful specifically when the winner's choice itself is in question.

Key features

queryPlanner (default): plan shape only, zero execution — cheapest, answers "what would run"
executionStats: actually executes the winning plan — adds nReturned, totalKeysExamined, totalDocsExamined, executionTimeMillis
allPlansExecution: executes every candidate's trial, not just the winner — the only mode that populates full rejectedPlans stats
Higher verbosity costs more real execution time — allPlansExecution runs every candidate, not just one
None of the three modes read from or write to the query plan cache

Verbosity → what it costs → what it answers

Verbosity → what it costs → what it answers
Mode	Executes?	Best for
queryPlanner	no	"what plan would run" — cheapest check
executionStats	yes, the winner	"how much work did the real query do"
allPlansExecution	yes, every candidate	"why did this plan win over that one"

Verbosity by cost and what it reveals

real numbersplan shape only

no executionexecutes every candidate

queryPlanner: cheapest — "what would run"
executionStats: runs the winner — real numbers
allPlansExecution: runs every candidate — "why did this win"

Command

text

db.<collection>.find({...}).explain(<"queryPlanner" | "executionStats" | "allPlansExecution">)

queryPlanner: plan shape, no execution. executionStats: runs the winner, real numbers. allPlansExecution: runs every candidate — reserve it for "why did this plan win" questions specifically, since it costs the most.

2.Reading executionStats: the key numbers

Four numbers in explain("executionStats").executionStats tell the real story: nReturned (what the app got), totalKeysExamined (index entries checked), totalDocsExamined (documents checked), and executionTimeMillis (real time taken). The ratios of the first three to each other reveal how well-aligned the plan actually is.

Key features

nReturned: documents actually returned to the caller
totalKeysExamined: index entries the plan checked to find candidates
totalDocsExamined: full documents fetched to verify or return (via FETCH)
executionTimeMillis: real wall-clock time for this specific execution — varies run to run, use as a signal not an exact figure
Ratio near 1 (examined ≈ returned) is well-aligned; a large ratio means the index (or lack of one) is not narrowing enough

Reading the four numbers together

Reading the four numbers together
Numbers	What it means
totalKeysExamined ≈ nReturned	the index narrowed almost exactly to the real matches
totalKeysExamined ≫ nReturned	the index is not selective enough for this query — many false candidates checked
totalDocsExamined ≫ totalKeysExamined	many index matches needed a full document fetch to be confirmed or used
totalDocsExamined = totalKeysExamined = nReturned	the ideal case — often only possible with a covered query

Cost paid vs. the goal

totalKeysExamined

index entries checked

totalDocsExamined

documents fetched

nReturned

the actual goal

Command

text

db.<collection>.find({...}).explain("executionStats").executionStats

nReturned is the goal; totalKeysExamined and totalDocsExamined are the cost paid to reach it. A ratio near 1 means well-aligned. executionTimeMillis varies run to run — trust the ratio over a single timing sample.

3.Recognizing the "examine many, return few" pattern

This is the practical symptom to watch for across an application: a query with a small nReturned but a large totalDocsExamined or totalKeysExamined. It shows up in slow-query logs and the profiler, not just in one-off explain() calls, and is usually fixable with a better-aligned index.

Key features

The pattern: nReturned is small, but totalDocsExamined or totalKeysExamined is large by comparison
Shows up in the slow-query log and the database profiler (system.profile), not only in individual explain() calls
Usually indicates a missing or misaligned index for the query's actual filter fields
A high totalDocsExamined specifically (vs. totalKeysExamined) can mean the index only partially narrows the query — see compound index design
Worth triaging across many logged queries, not just the one currently being investigated — the same misalignment often repeats

Command

text

// In a slow-query log or system.profile entry: compare docsExamined/keysExamined to nreturned directly

A small nReturned against a large totalDocsExamined/totalKeysExamined is the "examine many, return few" pattern — recognizable from a slow-query log line directly, usually fixable with a better-aligned index.

4.Comparing plans before and after an index change

The direct way to confirm an index change actually helped: run explain("executionStats") on the target query before creating the index, save the numbers, create the index, run it again, and compare. Anything less than an explicit before/after comparison is an assumption, not a verification.

Key features

Capture explain("executionStats") on the target query before making any index change
Make the change, then run the identical query with the identical explain() call again
Compare nReturned, totalKeysExamined, totalDocsExamined, and the stage tree shape — not just executionTimeMillis alone
A genuinely successful change shows the scan-to-return ratio moving toward 1, and often a stage (like SORT) disappearing entirely
If the "after" plan still shows the old index winning, the new index may need to be dropped, redesigned, or the old one removed

Command

text

// Before: capture explain("executionStats"). Make the change. After: same query, same explain() call, compare.

Capture explain("executionStats") before an index change, make the change, run the identical query again, and compare the full stage tree and numbers — not just "it feels faster." A vanished SORT stage is stronger evidence than a smaller timing number alone.

5.The database profiler, for production-scale diagnosis

db.setProfilingLevel(1, { slowms: 100 }) logs every operation slower than 100ms into the capped system.profile collection — real production traffic and data volume, not a local guess. Level 2 logs everything (expensive); level 0 (default) is off. explain() on one query is a hypothesis; the profiler on real traffic is the evidence.

Key features

db.setProfilingLevel(1, { slowms: 100 }) logs operations slower than 100ms — level 1 is the practical default for production
db.setProfilingLevel(2) logs every operation — high overhead, generally reserved for short, targeted diagnosis windows
Profile data lands in system.profile, a capped collection (default 1MB, holds a few thousand entries)
db.system.profile.find({ millis: { $gt: N } }).sort({ ts: -1 }) surfaces the slowest recent real operations
MongoDB Atlas offers a managed Query Profiler and Performance Advisor as higher-level alternatives to querying system.profile directly

Diagnosing with real production evidence

Enable the profiler

setProfilingLevel(1, { slowms: 100 })

Real traffic runs

actual load, actual data volume, actual concurrency

Slow ops logged

into the capped system.profile collection

Query the evidence

system.profile.find({ millis: { $gt: N } })

Command

text

db.setProfilingLevel(1, { slowms: 100 })
db.system.profile.find({ millis: { $gt: 100 } }).sort({ ts: -1 })

The profiler (db.setProfilingLevel) watches real production traffic and data volume — level 1 with a slowms threshold for ongoing use, level 2 only for short targeted windows. explain() checks one hypothesis; the profiler is the evidence from real traffic.

Spotted a mistake on this page? Tell us.

Read Sheet