Read explain plans as evidence: separate planner intent from completed execution, then prove scans, fetches, sorts, and coverage with AtlasMart queries.

COLLSCAN, IXSCAN, FETCH, SORT, COVERED Plans, and Reading explain Execution Stats

Read MongoDB explain evidence from physical plan stages through completed execution statistics, then connect scan/fetch/sort/coverage to measured work.

Intermediate110–150 minutesCOLLSCAN/IXSCAN/FETCH/SORT/covered explain labMongoDB 8.3.8 · mongosh 2.10.0Last reviewed: September 2026

Learning objectives

Distinguish queryPlanner, executionStats, and allPlansExecution evidence.

Read COLLSCAN, IXSCAN, FETCH, and explicit SORT as mechanisms rather than “good/bad” labels.

Use nReturned, totalKeysExamined, and totalDocsExamined to quantify work.

Prove a covered query by observing zero collection-document fetches instead of trusting an index name.

Avoid treating one local executionTimeMillis value as a production latency claim.

Reproducible lab baseline

This lesson pins MongoDB Community Server 8.3.8 with mongodb/mongodb-community-server:8.3.8-ubuntu2204-slim and mongosh 2.10.0. The server is a disposable standalone published only on loopback 127.0.0.1:27072. Authentication and TLS are disabled only for this isolated local lab. Feature Compatibility Version (FCV) is observed but never changed. Default read/write concern and primary read preference apply. Atlas, Search, KMS, and Enterprise Advanced are not mandatory. MongoDB 8.3 explain output can differ between the classic and slot-based execution engines; stage nesting and additional cost/memory fields are not a stable API. Runtime measurements are not pre-filled: this generation environment has no Docker/mongod/mongosh runtime, so learners must record the values produced on their own machine.

Planner intent is not completed execution

queryPlanner describes selection. executionStats executes the winning read plan to completion and reports the work done. allPlansExecution also exposes partial trial-period statistics for rejected candidates. The explain document format is explicitly not guaranteed stable, so the lab extracts invariant fields instead of depending on one pretty-printed tree.

1. AtlasMart problem: a correct result can still scan too much

AtlasMart's order dashboard asks for the newest 25 shipped orders for one tenant. A collection scan (COLLSCAN) examines collection documents directly. An index scan (IXSCAN) walks index keys. A FETCH means an index identified candidate record locations but MongoDB still had to read collection documents. An explicit SORT means the index did not provide the requested order. A covered query is answered from index keys without reading collection documents.

Evidence	What it proves	What it does not prove
`COLLSCAN`	Collection documents were scanned.	That the query is automatically unacceptable; small/full-scan workloads can be rational.
`IXSCAN`	An index supplied candidate keys/order.	That few keys were examined or no document fetch occurred.
`FETCH`	Collection documents were read after index navigation.	That FETCH is inherently bad; many indexed queries legitimately fetch.
No explicit `SORT`	The plan did not need a separate sort stage.	That latency is low under production concurrency.
`totalDocsExamined:0` with indexed filter/projection	Strong covered-query evidence.	That the wider application request is covered end-to-end.

bash · isolated Chapter 12 Lesson 1 lab setup

docker rm -f atlasmart-mongo-ch12-l1 2>/dev/null || truedocker volume rm atlasmart-mongo-ch12-l1-data 2>/dev/null || truedocker run -d --name atlasmart-mongo-ch12-l1 \  -p 127.0.0.1:27072:27017 \  -v atlasmart-mongo-ch12-l1-data:/data/db \  mongodb/mongodb-community-server:8.3.8-ubuntu2204-slimmongosh "mongodb://127.0.0.1:27072/atlasmart?directConnection=true" --quiet --eval \'printjson({server:db.version(),hello:db.hello().isWritablePrimary}); printjson(db.getSiblingDB("admin").runCommand({getParameter:1,featureCompatibilityVersion:1}))'

2. Seed enough data to make work visible

A twenty-thousand-document fixture is still tiny by production standards, but it is large enough to make scan counts useful. The distribution is deterministic so result cardinality is repeatable; cache state and elapsed milliseconds are intentionally not predicted.

javascript · deterministic order fixture

const c=db.orders_ch12_l1;c.drop();const base=new Date("2026-09-01T00:00:00Z");let batch=[];for (let i=0;i<20000;i++) {  batch.push({    _id:i,    tenantId:`tenant-${i%20}`,    orderNo:`ORD-${String(i).padStart(6,"0")}`,    status:["new","paid","packed","shipped"][i%4],    createdAt:new Date(base.getTime()+i*60000),    totalCents:1000+(i%9000),    warehouse:`w-${i%5}`  });  if (batch.length===1000) { c.insertMany(batch); batch=[]; }}if (batch.length) c.insertMany(batch);printjson({count:c.countDocuments({}),indexes:c.getIndexes().map(x=>x.name)});

text · deterministic fixture facts

Collection count: 20,000.There are 20 tenants and 4 status values in a repeating pattern.The target query asks for one tenant + one status, sorted newest-first, limited to 25.Do not copy any elapsed-time number into documentation; record what your host produces.

3. Compare three mechanisms, not three slogans

The first run has only the mandatory _id index. The second index supports equality predicates plus sort, but the requested projection still needs totalCents and orderNo from the collection. The third index includes those projected fields and is used with a diagnostic hint() to make the coverage experiment deterministic. A hint in this lab is evidence isolation—not a recommendation to hard-code hints in application code.

javascript · before, indexed-fetch, and covered-query evidence

const c=db.orders_ch12_l1;const filter={tenantId:"tenant-7",status:"shipped"};const sort={createdAt:-1};const projection={_id:0,orderNo:1,createdAt:1,totalCents:1};function stagesOf(explainDoc) {  const out=[];  const seen=new Set();  function walk(x) {    if (!x || typeof x !== "object" || seen.has(x)) return;    seen.add(x);    if (typeof x.stage === "string") out.push(x.stage);    for (const v of Object.values(x)) {      if (v && typeof v === "object") walk(v);    }  }  walk(explainDoc.queryPlanner?.winningPlan);  return [...new Set(out)];}function summary(explainDoc) {  return {    stages: stagesOf(explainDoc),    nReturned: explainDoc.executionStats?.nReturned,    totalKeysExamined: explainDoc.executionStats?.totalKeysExamined,    totalDocsExamined: explainDoc.executionStats?.totalDocsExamined,    executionTimeMillis: explainDoc.executionStats?.executionTimeMillis,    planCacheShapeHash: explainDoc.queryPlanner?.planCacheShapeHash,    planCacheKey: explainDoc.queryPlanner?.planCacheKey,    queryShapeHash: explainDoc.queryShapeHash  };}const before=c.find(filter,projection).sort(sort).limit(25).explain("executionStats");print("BEFORE INDEX"); printjson(summary(before));c.createIndex({tenantId:1,status:1,createdAt:-1},{name:"idx_filter_sort"});const fetched=c.find(filter,projection).sort(sort).limit(25).explain("executionStats");print("FILTER+SORT INDEX"); printjson(summary(fetched));c.createIndex({tenantId:1,status:1,createdAt:-1,orderNo:1,totalCents:1},{name:"idx_cover_dashboard"});const covered=c.find(filter,projection).sort(sort).limit(25).hint("idx_cover_dashboard").explain("executionStats");print("COVERING CANDIDATE"); printjson(summary(covered));

Expected structural evidence

Before the index, expect a collection-scan path and usually an explicit sort. With idx_filter_sort, expect index navigation and document fetches. With the covering candidate, verify totalDocsExamined becomes zero and no collection fetch is needed. Exact stage nesting, cost estimates, and timing depend on engine/version/runtime and must be read from your output.

4. Deliberately wrong: tune from a stage label or one timing sample

“IXSCAN is present, therefore this is fast” is incomplete. A low-selectivity index can scan many keys and fetch many documents. Conversely, one measured 1 ms run may simply reflect warm cache and an idle laptop. The repair is to preserve the query result, compare keys/documents examined, check sort/coverage, and then measure a distribution under a stable workload.

Explain has a cache boundary

explain() intentionally ignores existing plan-cache entries and prevents the explained winner from being cached. Lesson 2 therefore uses normal query executions—not explain—to observe cache state.

5. Verification, production judgment, and cleanup

Confirm all three query variants return the same newest 25 business rows.
Record stage names, nReturned, keys examined, and documents examined for each variant.
Verify the filter+sort index removes the need for a separate sort even if it still fetches documents.
Verify the covering candidate examines zero collection documents for this exact projection.
Do not infer production p95/p99 from this isolated standalone.

Production judgment. A covered query can reduce document reads but costs more index storage and write amplification. Favor it only for sufficiently important query shapes. On replicas/shards, plan evidence is member/topology-sensitive; on multi-tenant systems, tenant predicates remain authorization requirements independent of index shape. Keep before/after evidence, rollback the added index if the write/storage cost is unjustified, and bridge to Lesson 2 by asking how the planner selects among several plausible indexes.

bash · cleanup / full reset

docker rm -f atlasmart-mongo-ch12-l1docker volume rm atlasmart-mongo-ch12-l1-data

Check your understanding

Which explain mode actually completes the winning read plan?
Does IXSCAN guarantee few documents are fetched?
What is stronger evidence of coverage than the absence of a FETCH label?
Why not benchmark with a single executionTimeMillis?
Does explain populate the plan cache?

Review the answers

1. executionStats (and allPlansExecution) executes the winning read plan; queryPlanner only reports planning.

2. No. Read totalKeysExamined, totalDocsExamined, filters, and result count.

3. For this query, totalDocsExamined: 0 together with an index-supported filter/projection is strong coverage evidence.

4. One observation is dominated by cache, scheduling, host load, and noise; distributions under controlled conditions are more defensible.

5. No. Explain ignores existing entries and prevents caching of the explained winning plan.

Authoritative references

MongoDB 8.3 release notes — Current stable series, patch-sensitive behavior, 8.3 query-planning/profiling additions.
Explain command — Verbosity modes and explain behavior.
Explain results — Plan stages, execution statistics, query-shape hashes, and version-dependent output.
Query plans — Candidate selection, cost-based ranker backup, plan-cache states, and cache invalidation.
PlanCache.list() — Current mongosh plan-cache inspection interface.
$planCacheStats — Plan-cache documents and engine-dependent output.
Database profiler — Profiler levels, overhead, filters, thresholds, and security considerations.
$currentOp — Preferred live-operation inspection stage.
Slow query monitoring — Profiler/currentOp diagnostic workflow.
$queryStats — Managed-deployment query-shape statistics and stability/availability caveats.
Query shapes — MongoDB 8.x query-shape and plan-cache-shape terminology.

Keep knowledge open

Help the academy stay free and grow.

If these tutorials save you time, a small donation supports new lessons, technical review, diagrams, examples, and long-term maintenance.

ETHEthereum / ERC-20 only

0x716c4Ab160C4B66F31a28AE2448BfF68fc3a2ef0

Send only Ethereum or ERC-20 compatible assets to this address.