skip to content

explain(), Selectivity & ESR

9 roadmaps6 questions

Reading the plan the server actually chose and turning it into a fix. The ESR rule for ordering compound-index fields is the single most useful piece of MongoDB tuning lore an interviewer can ask you to recite and justify.

part ofMongoDBoverview, primer and where to startread it →
on this pageshow

explore

questions

6

In MongoDB explain() output, what is the difference between a COLLSCAN and an IXSCAN stage?

level: juniorimportance: must knowfreq 78%

answer

  1. Two names describe how rows were reached
  2. One reads documents, one reads keys
  3. Look at the stage on winningPlan
  4. The default verbosity does not run the query

basics

~20 s

COLLSCAN means the server read every document in the collection; IXSCAN means it walked index keys and touched only matching entries. A COLLSCAN on a filtered query over a large collection signals a missing index.

solid answer

~40 s

`explain()` returns the plan the query planner chose, as a tree of stages read from the innermost stage outward. A **COLLSCAN** stage means the server scanned the whole collection and applied the filter to each document — fine on a tiny collection or when the query genuinely matches most of it, a red flag otherwise. An **IXSCAN** stage means the server walked an index's keys within computed `indexBounds` and produced only the entries in range; it usually sits under a **FETCH** stage that loads the actual documents for those keys. To see the chosen plan alone call `.explain()` (verbosity `queryPlanner`, the default); to see what the query actually did — timings and counters — call `.explain("executionStats")`. `queryPlanner` does not execute the query.

code

javascript · 5 lines
javascript
// default verbosity: plan only, query is not executed
db.orders.find({ status: "ACTIVE" }).explain()

// runs the winning plan and reports counters
db.orders.find({ status: "ACTIVE" }).explain("executionStats")

go deeper

for a junior

Be ready to run explain() on a find() and say out loud which stage won and what it means. Knowing that COLLSCAN equals reading everything and IXSCAN equals walking an index is the expected floor.

for a middle

Explain the plan tree: stages nest through inputStage and execute innermost first, IXSCAN emits keys, FETCH loads documents, and indexBounds shows how much of the index the scan spans.

for a senior

Show that you check which index actually won, not just that an index won, and that you reach for executionStats rather than judging a plan from queryPlanner alone.

for a principal

Own the practice around it: which queries get a captured plan in code review, how plan output is collected from production, and when a collection scan is an accepted design choice rather than a defect.

## What explain() gives you `explain()` asks the server to describe how it would run — or did run — a query, instead of (or in addition to) returning results. It is available on `find()`, and as a database command for `update`, `delete`, `count`, `distinct` and aggregation. It takes a verbosity: - **`queryPlanner`** (the default): the planner picks a winning plan and describes it. **The query is not executed.** - **`executionStats`**: the winning plan is executed and per-stage counters plus `executionTimeMillis` are reported. - **`allPlansExecution`**: also reports the statistics gathered for the rejected candidate plans during plan selection. The plan itself is a tree. `queryPlanner.winningPlan` is the root; each node has a `stage` name and, where it consumes rows from another stage, an `inputStage`. **Read the tree from the innermost `inputStage` outward** — that is execution order. Alongside `winningPlan`, `rejectedPlans` lists the other candidates the planner considered and discarded. ## COLLSCAN `stage: "COLLSCAN"` means a collection scan: the server reads every document in the collection and evaluates the query predicate against each one. In `executionStats` a collection scan shows `totalKeysExamined: 0` (no index was touched) and `totalDocsExamined` equal to the number of documents in the collection. A collection scan is not automatically a bug. It is the correct plan when: - the collection is small enough that a scan costs nothing; - the query matches a large fraction of the collection, so an index would add key reads on top of reading nearly everything anyway; - there is simply no index that applies — for example an unanchored regular expression, or a predicate on a field nobody indexed. It *is* a bug when a selective filter on a large, hot collection falls back to a scan because the index you assumed exists does not, or cannot be used as written. ## IXSCAN and FETCH `stage: "IXSCAN"` means an index scan. The node reports `indexName`, `keyPattern` and `indexBounds` — the range of index keys the scan covers, printed per field. Bounds are the single most useful diagnostic on this node: they show how much of the index the scan actually spans. An `IXSCAN` produces *index keys plus record locations*, not documents. So it is normally wrapped in a `FETCH` stage, which loads the full document for each key — that is where the extra I/O for a non-covered query is spent. A `FETCH` may also carry a residual `filter`: a predicate the index could not evaluate, applied only after the document is in memory. Other stages you meet around them: `SORT` (a blocking, in-memory sort because no index supplied the order), `LIMIT`, `SKIP`, `PROJECTION_SIMPLE` / `PROJECTION_COVERED`, and on a sharded cluster `SINGLE_SHARD` or `SHARD_MERGE` above per-shard plans. ## Reading it in practice Run `db.orders.find({ status: "ACTIVE" }).explain("executionStats")` and look at three things, in order: 1. **The stage names.** `COLLSCAN` on a selective filter over a big collection means "no usable index". 2. **`indexName` on the `IXSCAN`.** It tells you *which* index won — often not the one you built. 3. **The counters** under `executionStats`: `nReturned`, `totalKeysExamined`, `totalDocsExamined`. `IXSCAN` alone does not mean fast; a broad index scan can examine far more keys than it returns. A very common surprise is an `IXSCAN` on `{ _id: 1 }` or on some unrelated index: the planner will use *any* index it can, and a poor index scan can still beat a scan of a huge collection while being far slower than the index you meant to build. ## Version note In currently shipping versions (7.x/8.x) some queries execute on the slot-based engine, and the classic-shaped plan tree is nested one level deeper, under `winningPlan.queryPlan`. The stage names are the same; only the path to them differs, so navigate to the tree rather than assuming a fixed key path. ## What to do with the answer If you see `COLLSCAN` and you did not want one, the fix is an index whose leading field(s) match the query's equality predicates. If you see `IXSCAN` and the query is still slow, the plan name is not the problem — move on to the examined-versus-returned counters, because the issue is how *much* of the index and how many documents the scan is chewing through. ## A note on cost The reason the distinction matters so much is physical. A collection scan reads document data proportional to the whole collection, evicting other pages from cache as it goes; an index scan reads a small, dense, usually cache-resident structure and then follows a bounded number of pointers. Two plans that return the same three documents can differ by orders of magnitude in pages touched, and the stage name in the plan is the first place that difference becomes visible.

  • Does calling explain() with no arguments actually run the query?
    No. The default verbosity is `queryPlanner`: the server parses the query, enumerates candidate plans, picks a winner and describes it without returning documents. Only `executionStats` and `allPlansExecution` execute the winning plan (and, for `allPlansExecution`, the candidates during the trial period), which is why those two are the modes you use when you need real timings and examined counts.
  • Why does an IXSCAN usually have a FETCH stage above it, and when does it not?
    An index scan yields index keys and record identifiers, not documents, so a `FETCH` loads each document — needed whenever the query returns fields the index does not hold or must evaluate a predicate on an unindexed field. The `FETCH` disappears when the query is covered: every field in the predicate and the projection lives in the index, and `_id` is excluded or indexed.
  • Is a COLLSCAN ever the right plan?
    Yes. On a small collection the scan costs almost nothing, and on any collection where the predicate matches a large share of documents, going through an index means reading most of the keys *and* most of the documents. The planner scores plans by measured work during a trial period, so it will legitimately prefer a scan over a broad, unselective index.

saying these in an interview costs you the question

  • Says explain() executes the query in every verbosity mode
  • Reads COLLSCAN as an error rather than a plan choice
  • Assumes an IXSCAN automatically means the query is fast
  • Cannot say where the chosen stage appears in the output
  • Thinks creating an index guarantees the planner will use it

context

resolving links…

open as a page

What is the ESR rule for ordering the fields of a MongoDB compound index?

level: middleimportance: must knowfreq 68%

basics

~20 s

ESR orders compound-index keys as Equality first, then Sort fields, then Range fields. Equality pins the scan to a narrow contiguous key range, the sort fields then arrive already ordered, and range predicates go last because they destroy ordering for the keys after them.

open as a page

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

level: middleimportance: must knowfreq 72%

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.

open as a page

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

level: middleimportance: should knowfreq 50%

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.

open as a page

When would you attach hint() to a MongoDB query instead of letting the planner choose?

level: seniorimportance: should knowfreq 42%

basics

~20 s

Mainly as a diagnostic: hint() forces the planner to consider only the index you name, so you can compare plans with explain. hint({$natural: 1}) forces a collection scan. Pinning an index permanently in application code freezes a decision that data changes will invalidate.

open as a page

Why does MongoDB's planner rarely choose index intersection with AND_SORTED or AND_HASH stages?

level: seniorimportance: nice to knowfreq 24%

basics

~20 s

Intersection combines results from two index scans, then still fetches documents, and it cannot supply a sort order. A single compound index usually wins the planner's scoring because one scan yields the matching keys directly and can also satisfy the sort.

open as a page