skip to content

What do totalKeysExamined, totalDocsExamined and nReturned reveal about a MongoDB query?

level: middleimportance: must knowfreq 72%

answer

  1. Three counters, one ratio
  2. Compare work done against rows produced
  3. One counts index entries, one counts documents
  4. A gap between keys and returned means broad bounds
  5. Zero documents examined has its own meaning

basics

~20 s

They are the work-versus-result ratio of a query. nReturned is what the client got; totalKeysExamined is index entries read; totalDocsExamined is documents loaded. Ratios near 1:1:1 mean a well-matched index; large gaps mean wasted work.

solid answer

~50 s

These three counters come from `explain("executionStats")` and together they say how much work the server did per document returned. **`nReturned`** is the result count. **`totalKeysExamined`** is how many index entries the scan walked. **`totalDocsExamined`** is how many whole documents were loaded. The ideal is `totalKeysExamined ≈ totalDocsExamined ≈ nReturned`. If **keys ≫ returned**, the index scan covers too broad a range — usually the wrong leading field or a compound index whose field order does not match the predicate. If **docs ≫ returned but keys ≈ docs**, the index found the candidates but a residual predicate is being evaluated after the `FETCH`, so a field is missing from the index. If **keys is 0 and docs is large**, it is a `COLLSCAN`. `totalDocsExamined: 0` with a non-zero `nReturned` means the query was covered by the index.

code

javascript · 5 lines
javascript
const e = db.orders.find({ status: "ACTIVE", region: "EU" })
  .explain("executionStats").executionStats;

print(e.nReturned, e.totalKeysExamined, e.totalDocsExamined);
// 40 40000 40000  -> index found candidates, filter ran after FETCH

go deeper

for a junior

Know what the three counters mean literally and where to find them: they live under executionStats, which only appears if you pass executionStats to explain().

for a middle

Explain the ratios and what each mismatch implies: broad index bounds, a residual filter applied after FETCH, a collection scan, or a covered query.

for a senior

Turn the numbers into a concrete index change and prove it by re-running explain and comparing the same counters, while accounting for limit() and blocking sorts distorting them.

for a principal

Set the standard: which counters get captured for slow queries, what ratio triggers review, and when a high ratio is accepted because the query is inherently unselective.

## Where the numbers come from Run `db.coll.find(...).explain("executionStats")`. Under `executionStats` you get, for the winning plan as a whole: - **`nReturned`** — documents the plan produced. - **`totalKeysExamined`** — index entries read across all index scans in the plan. - **`totalDocsExamined`** — documents read from the collection. - **`executionTimeMillis`** — wall-clock time for the plan, including plan selection. The same counters appear per stage inside `executionStages` (`keysExamined` on `IXSCAN`, `docsExamined` on `FETCH` or `COLLSCAN`, plus `works`, `advanced`, `seeks`, `isEOF`), which is how you attribute the cost to a specific node when the plan has several. ## The ratio is the diagnosis Timing alone is a poor signal: it depends on cache state, concurrent load and hardware, so the same query can look fine on a warm cache and terrible at 3 a.m. The **examined-to-returned ratio is stable** — it describes the algorithm, not the machine. That is why interviewers ask for it: it is the number you can compare before and after a change and across environments. Read it as a funnel: keys examined → documents examined → documents returned. Every place the funnel narrows sharply is work thrown away. ### Pattern 1 — keys much larger than returned `nReturned: 12, totalKeysExamined: 480000, totalDocsExamined: 480000` The scan walked half a million index entries to produce twelve results. The index applies but the *bounds* are wide: check `indexBounds` on the `IXSCAN`. Typical causes are a leading field that is not selective (an index on `{ status: 1 }` where almost every document shares one status), a compound index whose leading field is not the one the query pins with equality, or a range predicate placed before the equality field so the scan spans a huge key range. The fix is index design, not more indexes: put the equality fields first. ### Pattern 2 — docs much larger than returned, keys about equal to docs `nReturned: 40, totalKeysExamined: 40000, totalDocsExamined: 40000` Same shape as above, but look at the `FETCH` stage: if it carries a `filter`, the index narrowed the candidates and then each of those 40 000 documents was loaded so a predicate on a *non-indexed* field could be evaluated. Adding that field to the compound index moves the test into the index scan, and both counters collapse toward `nReturned`. ### Pattern 3 — keys zero, docs large No index was used at all: this is a `COLLSCAN`, and `totalDocsExamined` equals the collection's document count. ### Pattern 4 — docs zero, returned above zero The query was **covered**: every field needed for the predicate and the projection lived in the index, so no document was ever loaded. ### Pattern 5 — keys larger than docs More keys than documents happens with a multikey (array) index, where one document contributes many index entries, or when a deduplicating stage sits above the scan. It is expected there, not automatically a defect. ## What good looks like There is no universal threshold, but a useful working rule: for a *selective* query, `totalKeysExamined / nReturned` should be close to 1, and anything in the hundreds or thousands deserves an explanation. For a query that legitimately matches most of the collection, a high ratio is inevitable and the right answer may be no index at all. Be careful with two mechanics: - **`limit()` changes the numbers.** A plan that stops early examines fewer keys, so a query with a limit can look healthy while the underlying scan is badly targeted — the same query without the limit tells the truth. - **`sort()` without index support** adds a blocking `SORT` stage, which must examine *all* matching documents before returning the first one, so the counters swell even though the predicate is selective. The `SORT` stage in the plan is the tell. ## From counters to a fix The repair sequence that follows from the numbers is mechanical: 1. Keys huge → widen or reorder the compound index so the equality predicates lead and the bounds tighten. 2. Docs huge with a residual `FETCH` filter → add the filtered field to the index. 3. Docs still equal to keys and you only need a couple of fields → extend the index so the query is covered and `totalDocsExamined` drops to zero. 4. Keys zero → create the index at all, or accept the scan if the query is genuinely unselective. Always re-run `explain("executionStats")` afterwards and compare the same three numbers. "It felt faster" is not evidence; the ratio moving from 480 000:12 to 12:12 is. ## Where else the numbers appear The same counters are recorded by the database profiler and by slow-query log lines, under the same names. That matters operationally: you do not have to reproduce a slow query by hand to get its ratio, and a captured `keysExamined` far above `nReturned` in a log line is enough to open an index-design conversation before anyone re-runs anything.

  • Why is the examined-to-returned ratio a better tuning signal than executionTimeMillis?
    Timing depends on cache warmth, disk speed and concurrent load, so it varies run to run and machine to machine. The examined counters describe the amount of work the plan inherently performs, so they are reproducible, comparable between environments, and they move only when the plan or the index genuinely changes. Use time to spot the problem and the ratio to prove the fix.
  • A plan shows totalKeysExamined equal to totalDocsExamined, both far above nReturned. What is the likely cause?
    The index supplied candidates but could not evaluate the whole predicate, so every candidate document was fetched and filtered afterwards. Look for a `filter` on the FETCH stage naming a field absent from the index. Adding that field to the compound index moves the test into the index scan and drops both counters toward nReturned.
  • How does adding limit() distort these numbers?
    A limit lets the plan stop as soon as enough documents are produced, so keysExamined and docsExamined reflect only the prefix of the scan that was consumed. A badly targeted query can therefore look healthy under a small limit and fall over when the limit rises or the matching documents sit late in the index. Check the plan without the limit too.

Think of it as a funnel with three openings: keys read, documents loaded, documents returned. A funnel that swallows half a million and drips out twelve is doing almost all of its work for nothing.

saying these in an interview costs you the question

  • Quotes executionTimeMillis alone as proof a query is healthy
  • Confuses totalDocsExamined with the collection's total document count
  • Believes nReturned counts documents scanned rather than returned
  • Ignores docsExamined because the plan already shows an IXSCAN
  • Adds more single-field indexes instead of fixing the bounds

context

resolving links…