MongoDB Performance: explain() and Query Optimization
Fast recall
Quick revision
Core answer
db.setProfilingLevel(1, { slowms: 100 })) surface which queries to diagnose first.Ability to use `explain()` end-to-end and translate plan output into actionable index or query changes
Only running `explain()` without `executionStats`, or ignoring the `keys examined / returned` ratio when an index exists but is inefficient
Walk through a full `executionStats` tree, call out `FETCH` vs `PROJECTION_COVERED`, the 100MB sort limit + `allowDiskUse`, and the ESR rule
Interview-ready guide
Interview answer
Start with the tool
explain('executionStats')for the plan plus runtime stats- Look for
IXSCANvsCOLLSCANand the keys-to-returned ratio
Apply ESR
- Equality → Sort → Range for compound-index field order
- Left-prefix rule for derived queries
Mention covered queries
- Include every filter, sort, and projected field in the index
- Drop
_idfrom the projection - Plan shows
PROJECTION_COVEREDandtotalDocsExamined: 0
Cover the operational tools
- Profiler
db.setProfilingLevel(1, { slowms: 100 }) - Atlas Performance Advisor
$indexStatsfor usage audits
Close with aggregation tips
$matchfirst to use indexesallowDiskUse(true)for > 100MB pipelines$merge/$outfor materialized views
MongoDB Performance: `explain()` and Query Optimization
Direct Answer
explain('executionStats') returns the query plan plus runtime metrics — the primary tool for slow-query diagnosis. Look for IXSCAN (good) vs COLLSCAN (bad), compare totalKeysExamined to nReturned (ratio near 1 is efficient), and watch for in-memory SORT stages that exceed 100MB. The usual fixes are adding an ESR-compliant compound index, turning a query into a covered query (projection + index fully satisfy it), restricting projections, and reducing cardinality early in aggregation pipelines. Atlas Performance Advisor and the database profiler (db.setProfilingLevel(1, { slowms: 100 })) surface which queries to diagnose first.
Common Mistakes & Pitfalls
Only running explain() without executionStats, or ignoring the keys examined / returned ratio when an index exists but is inefficient
Check your understanding
Answer these without looking back at the article.
- How do you detect a stale plan cache and force a re-evaluation?
- When is `$indexStats` useful for finding safe-to-drop indexes?
- What does `winningPlan` vs `rejectedPlans` in explain output tell you?
- How do you interpret `executionTimeMillisEstimate` in the execution stats tree?
Related interview prep categories
1Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.