In MongoDB explain() output, what is the difference between a COLLSCAN and an IXSCAN stage?
answer
- Two names describe how rows were reached
- One reads documents, one reads keys
- Look at the stage on winningPlan
- The default verbosity does not run the query
basics
~20 sCOLLSCAN 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// 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
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.
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.
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.
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