explain() and Performance Analysis

What we're doing: Match three real investigation questions to the correct verbosity level, showing the cost/benefit reasoning explicitly.

// Question: "will this new query use my new index at all?"
db.orders.find({ region: "west" }).explain("queryPlanner")   // cheapest — no need to actually run it

// Question: "this query feels slow — how much work is it really doing?"
db.orders.find({ status: "pending" }).explain("executionStats")   // real numbers, one execution

// Question: "I expected index A to win, but B did — why?"
db.orders.find({ status: "pending", region: "west" }).explain("allPlansExecution")  // compare both trials

2

A yes/no question about plan shape does not need real execution numbers — queryPlanner answers it for free.

5

A "why did X win over Y" question specifically needs the comparative data only allPlansExecution provides — the other two modes cannot answer it.

Why this works: Choosing verbosity by the actual question being asked, rather than defaulting to the highest level "to be safe," keeps performance investigation itself cheap and its output focused on what is actually needed.

What we're doing: Contrast a well-aligned query's numbers against a poorly-aligned one on the same collection, side by side.

// Well-aligned: filter matches the leading field of a compound index
db.orders.find({ status: "pending", region: "west" }).explain("executionStats").executionStats
// { nReturned: 118, totalKeysExamined: 120, totalDocsExamined: 118 }  -> ratio ≈ 1.02, well-aligned

// Poorly-aligned: filter uses only the second field of that same compound index
db.orders.find({ region: "west" }).explain("executionStats").executionStats
// { nReturned: 4500, totalKeysExamined: 4500, totalDocsExamined: 4500 }  -> looks fine alone, but see the mistake below

1

A ratio near 1 across all three numbers is the signature of a well-aligned plan — nearly everything examined was actually needed.

5

This second query's ratio also looks close to 1 in isolation — the real problem here is comparative, covered in the mistake below.

Why this works: Reading these four numbers together, rather than any single one in isolation, is what actually diagnoses a plan — a ratio near 1 confirms the index is well-matched to this specific query's filter, not just that some index was used.

What we're doing: Recognize the pattern from a slow-query log line, then confirm it with explain() before deciding on a fix.

// A slow-query log line (or system.profile entry):
// { ns: "shop.orders", nreturned: 12, keysExamined: 85000, docsExamined: 85000, millis: 340 }

// The pattern is visible from the log alone: 85,000 examined for 12 returned — worth investigating
db.orders.find({ /* the logged filter */ }).explain("executionStats")
// -> confirm the exact stage tree and which field is (or isn't) narrowing the search

1

The ratio (85,000 examined / 12 returned) is visible directly in the log line, without running explain() first — this is what makes triage from logs practical.

5

explain() on the same filter confirms exactly where the misalignment is (missing index, wrong field order) once the log has flagged the query as worth investigating.

Why this works: Being able to recognize this pattern from a log line means performance triage does not require running explain() on every single query in the system — the ratio itself, visible in logged metadata, is the trigger for deciding which queries are worth a closer look.

What we're doing: Show a full before/after comparison, including the stage-tree change, not just a single number.

// Before: db.orders.createIndex({ status: 1 }) only
db.orders.find({ status: "pending" }).sort({ createdAt: -1 }).explain("executionStats")
// -> stage tree: SORT -> FETCH -> IXSCAN ; totalDocsExamined: 4,500 ; nReturned: 4,500 (all fetched, then sorted)

// After: db.orders.createIndex({ status: 1, createdAt: -1 })
db.orders.find({ status: "pending" }).sort({ createdAt: -1 }).explain("executionStats")
// -> stage tree: FETCH -> IXSCAN (no SORT) ; totalDocsExamined: 4,500 ; same nReturned, but no in-memory sort step

1

The "before" capture is the baseline — without it, there is nothing concrete to compare the change against.

5

The compound index eliminates the SORT stage entirely — a structural change in the stage tree, not just a smaller number, and the clearest possible confirmation the change worked as intended.

Why this works: Comparing the full stage tree, not just one number, catches improvements (or regressions) a single metric can miss — a SORT stage disappearing is a qualitatively different kind of confirmation than a timing number simply looking a bit lower.

What we're doing: Enable profiling at a practical production threshold, then query the results for the worst real offenders.

db.setProfilingLevel(1, { slowms: 100 })   // log anything over 100ms

// later, investigate what's actually been slow in real production traffic:
db.system.profile.find({ millis: { $gt: 200 } })
  .sort({ ts: -1 })
  .limit(20)

1

Level 1 with a slowms threshold is a practical, ongoing setting — it only captures the operations actually worth investigating, not everything.

5

Querying system.profile directly surfaces real, recently-run slow operations — actual production filters, actual production data volume, not a local approximation of either.

Why this works: A local benchmark, however careful, cannot reproduce the concurrent load, real data distribution, and real query mix that only exists in production — the profiler observes reality directly instead of trying to simulate it, which is exactly why it is the tool for production diagnosis specifically.