What must be true for a MongoDB query to be covered, with explain reporting totalDocsExamined 0?

middleshould knowfreq 50%

answer

The plan never touches the collection
One index must hold everything asked for
A field is returned by default and ruins it
Arrays in the index break it too

basics

~20 s

Every field in the filter and the projection must live in one index, the projection must return nothing else, and _id must be excluded explicitly unless it is in the index. The index must not be multikey on a queried array field.

solid answer

~50 s

A query is covered when the server can answer it from index keys alone and never loads a document. Three conditions must hold together: all fields in the query predicate and all fields in the projection are keys of a single index; the projection returns no other field, which in practice means { _id: 0, ... } because _id is included by default and is usually not part of the index; and the index is not multikey for the fields involved — an index over an array field cannot cover a query returning that field, because the keys hold individual elements rather than the array. The evidence in explain("executionStats") is totalDocsExamined: 0, no FETCH stage, and a PROJECTION_COVERED stage above the IXSCAN. On a sharded collection the index must also include the shard-key fields, since the shard needs them to filter orphaned documents.

A query is **covered** when the server can answer it from index keys alone and never loads a document. Three conditions must hold together: all fields in the query predicate and all fields in the projection are keys of a **single** index; the projection returns **no other field**, which in practice means `{ _id: 0, ... }` because `_id` is included by default and is usually not part of the index; and the index is not **multikey** for the fields involved — an index over an array field cannot cover a query returning that field, because the keys hold individual elements rather than the array. The evidence in `explain("executionStats")` is `totalDocsExamined: 0`, no `FETCH` stage, and a `PROJECTION_COVERED` stage above the `IXSCAN`. On a sharded collection the index must also include the shard-key fields, since the shard needs them to filter orphaned documents.

code

javascript · 7 lines

javascript

db.users.createIndex({ country: 1, email: 1 })

// covered: totalDocsExamined 0
db.users.find({ country: "DE" }, { _id: 0, country: 1, email: 1 })

// not covered: _id is returned by default -> FETCH
db.users.find({ country: "DE" }, { country: 1, email: 1 })

go deeper

for a junior

Know the definition: a covered query is answered from index keys alone, so no document is read. Remember that _id comes back by default and must be excluded.

for a middle

State all the conditions and show the plan evidence — totalDocsExamined of 0, no FETCH stage, PROJECTION_COVERED — and explain why a multikey index cannot cover the array field.

for a senior

Weigh the tradeoff out loud: coverage is bought by widening an index, which costs cache and write throughput, and it can silently vanish when a projection changes or an array appears in the data.

for a principal

Decide where coverage is worth designing for across a workload, and how the team guards it — asserting docsExamined in tests, or accepting that a wider index is not affordable on a write-heavy collection.

What covered means

An index entry stores the indexed field values plus a pointer to the document. Normally a query reads index entries (IXSCAN) and then follows the pointers to load documents (FETCH) — that second step is random I/O and usually dominates the cost. A covered query skips it: everything the client asked for is already in the index keys, so the plan is IXSCAN then projection, and totalDocsExamined is 0.

The conditions

1. One index holds every field involved. Both the fields in the filter and the fields in the projection must be keys of the same index. Two indexes that together hold the fields do not cover a query.

2. The projection returns nothing else. MongoDB returns _id unless you exclude it, and _id is normally not a key of your compound index. So a query is almost always written { _id: 0, a: 1, b: 1 } to be covered — or the index includes _id explicitly. Equally, an inclusion projection listing a field the index lacks kills coverage, and so does no projection at all, since the default is the whole document.

3. The index is not multikey over the fields in play. When an indexed field contains arrays the index is multikey: it stores one key per element, and the original array cannot be reconstructed from them. So a multikey index cannot cover a query returning that array field. This is easy to miss because an index only becomes multikey when a document with an array is inserted — a query that was covered in a test dataset can silently stop being covered in production.

4. On a sharded collection, the shard key must be in the index. When results are read from a shard, orphaned documents are filtered out using the shard key, so unless the shard-key fields are in the index the shard has to fetch documents to check — and coverage is lost.

Reading the plan

With an index { country: 1, email: 1 }, the query db.users.find({ country: "DE" }, { _id: 0, country: 1, email: 1 }) produces a winning plan of PROJECTION_COVERED over an IXSCAN, and executionStats reports totalDocsExamined: 0 with totalKeysExamined equal to nReturned. Drop the _id: 0 and the plan gains a FETCH and a PROJECTION_SIMPLE, and totalDocsExamined jumps to the match count — one line of difference, a completely different amount of I/O.

Why it pays, and when it does not

The win is not shaving a constant factor. Skipping the fetch means the query touches only index pages, which are smaller and far more likely to be resident in cache than the documents; for a query returning thousands of results this can be the difference between a cached scan and thousands of random document reads. Covered queries shine for lookups that return a handful of small fields at high frequency — an id-to-name resolution, an existence check, a keyset-pagination cursor fetch.

The cost is index width. Every field you add to make a query covered is stored in every index entry, enlarging the index, consuming more cache, and adding work to every write that touches those fields. Widening a five-field index to eight fields so one report can be covered is often a bad trade on a write-heavy collection; widening a two-field index to three for a query on the hottest path is often an excellent one. Decide by measuring both the query's counters and the index's size against the working set you can keep in memory.

Common traps

Aggregation. A $match plus $project pipeline over an index can also avoid document fetches, but the rules are pipeline-specific and the plan output differs; do not promise coverage without checking that pipeline's own explain output.
Nested fields. Indexing a.b covers a projection of a.b, but projecting the whole a subdocument does not, because the index holds only the one leaf value.
Coverage is not permanence. A schema change that introduces an array into an indexed field, a projection gaining one more field in a code change, or a move to a sharded deployment can all quietly remove coverage. If it matters, assert it — check totalDocsExamined in a test.
Covered is not cached. Coverage is about where the data came from; the plan cache is about which plan was chosen. They are unrelated mechanisms and confusing them is a common tell.

Answering in an interview

Name all three conditions unprompted, mention _id explicitly (it is the trap the question is really probing), give the explain evidence (totalDocsExamined: 0, no FETCH), and finish with the tradeoff: coverage is bought with a wider index, paid for on every write.

## What covered means An index entry stores the indexed field values plus a pointer to the document. Normally a query reads index entries (`IXSCAN`) and then follows the pointers to load documents (`FETCH`) — that second step is random I/O and usually dominates the cost. A **covered query** skips it: everything the client asked for is already in the index keys, so the plan is `IXSCAN` then projection, and `totalDocsExamined` is `0`. ## The conditions **1. One index holds every field involved.** Both the fields in the filter *and* the fields in the projection must be keys of the same index. Two indexes that together hold the fields do not cover a query. **2. The projection returns nothing else.** MongoDB returns `_id` unless you exclude it, and `_id` is normally not a key of your compound index. So a query is almost always written `{ _id: 0, a: 1, b: 1 }` to be covered — or the index includes `_id` explicitly. Equally, an inclusion projection listing a field the index lacks kills coverage, and so does no projection at all, since the default is the whole document. **3. The index is not multikey over the fields in play.** When an indexed field contains arrays the index is multikey: it stores one key per element, and the original array cannot be reconstructed from them. So a multikey index cannot cover a query returning that array field. This is easy to miss because an index only becomes multikey when a document with an array is inserted — a query that was covered in a test dataset can silently stop being covered in production. **4. On a sharded collection, the shard key must be in the index.** When results are read from a shard, orphaned documents are filtered out using the shard key, so unless the shard-key fields are in the index the shard has to fetch documents to check — and coverage is lost. ## Reading the plan With an index `{ country: 1, email: 1 }`, the query `db.users.find({ country: "DE" }, { _id: 0, country: 1, email: 1 })` produces a winning plan of `PROJECTION_COVERED` over an `IXSCAN`, and `executionStats` reports `totalDocsExamined: 0` with `totalKeysExamined` equal to `nReturned`. Drop the `_id: 0` and the plan gains a `FETCH` and a `PROJECTION_SIMPLE`, and `totalDocsExamined` jumps to the match count — one line of difference, a completely different amount of I/O. ## Why it pays, and when it does not The win is not shaving a constant factor. Skipping the fetch means the query touches only index pages, which are smaller and far more likely to be resident in cache than the documents; for a query returning thousands of results this can be the difference between a cached scan and thousands of random document reads. Covered queries shine for lookups that return a handful of small fields at high frequency — an id-to-name resolution, an existence check, a keyset-pagination cursor fetch. The cost is index width. Every field you add to make a query covered is stored in every index entry, enlarging the index, consuming more cache, and adding work to every write that touches those fields. Widening a five-field index to eight fields so one report can be covered is often a bad trade on a write-heavy collection; widening a two-field index to three for a query on the hottest path is often an excellent one. Decide by measuring both the query's counters and the index's size against the working set you can keep in memory. ## Common traps - **Aggregation.** A `$match` plus `$project` pipeline over an index can also avoid document fetches, but the rules are pipeline-specific and the plan output differs; do not promise coverage without checking that pipeline's own explain output. - **Nested fields.** Indexing `a.b` covers a projection of `a.b`, but projecting the whole `a` subdocument does not, because the index holds only the one leaf value. - **Coverage is not permanence.** A schema change that introduces an array into an indexed field, a projection gaining one more field in a code change, or a move to a sharded deployment can all quietly remove coverage. If it matters, assert it — check `totalDocsExamined` in a test. - **Covered is not cached.** Coverage is about where the data came from; the plan cache is about which plan was chosen. They are unrelated mechanisms and confusing them is a common tell. ## Answering in an interview Name all three conditions unprompted, mention `_id` explicitly (it is the trap the question is really probing), give the explain evidence (`totalDocsExamined: 0`, no `FETCH`), and finish with the tradeoff: coverage is bought with a wider index, paid for on every write.

Why does a multikey index fail to cover a query on the array field?
A multikey index stores one key per array element, not the array itself, so the original array — its length, order and duplicates — cannot be reconstructed from index entries. The server must load the document to return that field. Note the index becomes multikey the moment any document stores an array there, so coverage can disappear without a code change.
How do you tell from explain that a query is covered rather than merely indexed?
Look for totalDocsExamined equal to 0 in executionStats and the absence of a FETCH stage in the winning plan; the projection appears as PROJECTION_COVERED sitting directly on the IXSCAN. An indexed-but-not-covered query shows FETCH above IXSCAN and a docsExamined count matching the number of index entries that passed the bounds.
What is the cost of extending an index just to make one query covered?
Every added field is stored in every index entry, so the index grows, occupies more cache, and every insert or update touching those fields does more work. It pays for a hot, high-frequency query returning a couple of small fields; it rarely pays for a wide report on a write-heavy collection. Measure the index size and the write path, not only the read.

saying these in an interview costs you the question

Forgets _id is returned by default and breaks coverage
Thinks any query that uses an index is a covered query
Claims a multikey index can cover a query on the array field
Says a covered query still needs its FETCH stage
Assumes two indexes together can cover one query

What must be true for a MongoDB query to be covered, with explain reporting totalDocsExamined 0?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

What covered means

The conditions

Reading the plan

Why it pays, and when it does not

Common traps

Answering in an interview

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions5 questions

▾related topics3 topics

What covered means

The conditions

Reading the plan

Why it pays, and when it does not

Common traps

Answering in an interview

answer

go deeper

context