A query that takes 900 ms in production and 4 ms on your laptop is almost always reading far more data than it returns. explain tells you how much and where. Interviewers at the senior level often hand you a chunk of explain output and ask what’s wrong. Reading it well comes down to a handful of fields and a sense of what “good” looks like.
The output fragments below are trimmed and illustrative for MongoDB 7 and 8; no server was available while writing. Field names match the documented explain format.
The three verbosity modes
db.orders.find({ customerId: 42 }).explain() // "queryPlanner"
db.orders.find({ customerId: 42 }).explain("executionStats")
db.orders.find({ customerId: 42 }).explain("allPlansExecution")- queryPlanner (the default) shows the winning plan and rejected plans. It doesn’t run the query, so there are no counts. Useful for a quick “is it using an index?”.
- executionStats runs the winning plan to completion and reports counts and time. This is the one you want almost every time.
- allPlansExecution also includes the partial stats from the plan trial, where candidate plans raced each other. Useful when you’re asking “why did it pick that index?”.
Because executionStats executes the query, don’t run it casually on an expensive aggregation against a busy primary. For writes, explain on updateOne or deleteMany doesn’t actually modify data, but it does run the read side.
The three numbers that matter
Here’s a trimmed result for a slow query:
db.orders.find({ customerId: 42, status: "shipped" })
.sort({ createdAt: -1 }).limit(20)
.explain("executionStats")
executionStats: {
nReturned: 20,
executionTimeMillis: 870,
totalKeysExamined: 412000,
totalDocsExamined: 412000,
executionStages: { stage: "LIMIT", inputStage: { stage: "FETCH",
filter: { $and: [ { customerId: { $eq: 42 } }, { status: { $eq: "shipped" } } ] },
inputStage: { stage: "IXSCAN", keyPattern: { createdAt: -1 }, ... } } }
}Read it like this:
- nReturned: 20. What the app got.
- totalKeysExamined: 412,000. Index entries walked.
- totalDocsExamined: 412,000. Documents loaded from storage.
The ratio is the story. MongoDB walked the createdAt index newest first (so no sort was needed), loaded each document, checked whether it belonged to customer 42 and was shipped, and threw away 411,980 of them. The filter sitting on the FETCH stage confirms it: the conditions were applied after loading documents, not inside the index.
The fix is an index where the equality fields come first: { customerId: 1, status: 1, createdAt: -1 }. After creating it, the same explain should show something close to nReturned: 20, totalKeysExamined: 20, totalDocsExamined: 20.
A rough reading guide:
| Pattern | What it means |
|---|---|
| keys ≈ docs ≈ returned | Index fits the query. Good. |
| keys ≈ docs, both ≫ returned | Index used for order or part of the filter; the rest is filtered after FETCH. |
| keys ≫ docs ≈ returned | Index has the right fields but in an order that forces skipping many keys (often a range before an equality). |
| docs examined, keys = 0 | Collection scan. |
| docs = 0, keys ≈ returned | Covered query: answered from the index alone. |
Stage names you should recognise
Explain output is a tree, read from the innermost inputStage outwards.
- COLLSCAN: reads every document. Fine for a 200-document config collection; a red flag on anything large.
- IXSCAN: walks an index. Check
keyPatternandindexBoundsto see which index and which ranges. - FETCH: loads full documents for the keys found. A
filteron this stage means some conditions couldn’t be checked in the index. - SORT: an in-memory sort. Blocking, uses memory (100 MB limit before spilling or failing), and usually means the index order doesn’t match the sort. With a limit you’ll see it as a top-k sort, which is cheaper but still not free.
- PROJECTION_COVERED: projection served from index keys, with no FETCH. The best case.
- SORT_MERGE / OR: from
$orqueries using several indexes. - SHARD_MERGE / SINGLE_SHARD: on a sharded cluster, whether the query went to all shards or one.
MongoDB 8.0 also added EXPRESS plans (stage names like EXPRESS_IXSCAN) for simple equality lookups such as findOne({ _id }), which skip the normal planning. If you see them, it’s a fast path, not a problem.
Index bounds tell you how tight the scan is
indexBounds in the IXSCAN stage shows exactly which ranges were scanned:
indexBounds: {
customerId: [ "[42, 42]" ],
status: [ "[\"shipped\", \"shipped\"]" ],
createdAt: [ "[MaxKey, MinKey]" ]
}Point bounds [42, 42] on the leading fields and a full range on the sort field is ideal: one contiguous slice of the index. If you see [MinKey, MaxKey] on the first field, the index’s leading field isn’t constrained at all, and MongoDB is scanning the whole index.
Why the planner picked a bad index
MongoDB caches plans per query shape. When a shape is first seen, candidate plans run a short trial and the one that produces results fastest wins. That works most of the time, but a plan chosen for one parameter can be cached and reused for a very different one. A query for a tiny customer picks the customerId index; a query for a customer with 2 million orders might have been better off with another. If allPlansExecution shows the trial was close, or performance varies wildly by parameter, look at the plan cache (db.orders.getPlanCache().list()), and consider whether a better compound index removes the ambiguity. hint() forces an index, but treat it as a last resort, because it stops the planner adapting when data changes.
Explain is only as honest as your data
On a dev database with 300 orders, a collection scan takes 1 ms and looks fine. The counts still reveal it (totalDocsExamined: 300 for 2 results), so read the ratios, not the time. For production, turn on the profiler for slow operations (db.setProfilingLevel(1, { slowms: 100 })) or read slow query lines in the server log; both record keysExamined, docsExamined and planSummary for real traffic. Atlas users get the same data in the Query Profiler and Performance Advisor.
Interview practice
Given this explain summary for db.events.find({ type: "click", ts: { $gte: t } }).sort({ ts: -1 }).limit(50), which uses an index on { ts: -1, type: 1 }:
nReturned: 50, totalKeysExamined: 61000, totalDocsExamined: 50,
stages: LIMIT <- FETCH <- IXSCAN { ts: -1, type: 1 }What’s happening, is it a problem, and what would you change?
Answer
Docs examined equals returned, so every document MongoDB loaded was a match: the type filter is being applied inside the index, before fetching. That’s why totalDocsExamined is 50. But it walked 61,000 index keys to find those 50. The index leads with ts, so MongoDB reads keys newest first and checks type on each key; clicks are apparently about 1 in 1,200 events, so it skips a lot of non-click keys.
Whether it’s a problem depends on latency. Walking keys is much cheaper than fetching documents, so this might be acceptable. But following the equality, sort, range guideline, { type: 1, ts: -1 } would put all clicks together, already in time order. The bounds would be type: ["click","click"], ts: [MaxKey, t], and the scan would stop after 50 keys. I’d expect totalKeysExamined to drop to about 50. I’d create that index, confirm with explain on production-sized data, and then check whether the old { ts: -1, type: 1 } index is still used by anything else before dropping it.
Further reading
- MongoDB interview questions
- MongoDB MCQs
- MongoDB Compound Indexes and Query Order
- MongoDB Index Hints and Plan Selection
- Official docs: Explain results