MongoDB — explain() Reveals COLLSCAN on a Hot Query

Fix MongoDB queries doing full collection scans, read explain() stage trees, build compound indexes respecting ESR order, and cover the query to avoid FETCH.

intermediatedebuggingmongodbindexesquery-planning

Symptoms

db.collection.find(...).explain() shows COLLSCAN as the winning stage with totalDocsExamined equal to collection size
Slow query profiler entries flag full scans on collections with millions of documents
Latency grows with collection size even though the predicate is highly selective

Possible causes

No index covers the query predicate field or the leading field of the compound index does not match
The query uses an operator the index cannot serve (case-insensitive regex without prefix, $where, negation-heavy predicates)
A sort without index support triggers an in-memory sort with blocking SCAN_AND_SORT
The compound index has the right fields in the wrong order relative to the query shape

Diagnosis steps

Read the winning plan

mongosh --quiet --eval 'db.orders.find({status: "paid", customerId: 42}).sort({createdAt: -1}).limit(50).explain("executionStats")' | head -60

Interpretation

The stage tree names the access path: COLLSCAN, IXSCAN with FETCH, or PROJECTION_COVERED; executionStats show docsExamined versus nReturned — the selectivity gap.

Check existing indexes against the shape

mongosh --quiet --eval 'db.orders.getIndexes()'

Interpretation

List indexes and compare field order with the query: equality fields first, then sort, then range — ESR.

Rebuild the index in the right order

mongosh --eval 'db.orders.createIndex({customerId: 1, status: 1, createdAt: -1})'

Interpretation

A matching compound index should turn the plan into IXSCAN with limit honored, docsExamined collapsing to near nReturned.

Root cause

Without an index whose leading fields serve the query predicates, MongoDB must examine every document in the collection to apply the filter. Each examination deserializes the document, so query cost scales linearly with collection size regardless of predicate selectivity or the limit.

Fix

Build compound indexes following ESR order (Equality, Sort, Range), keep leading fields aligned with the equality predicates of the hot query shape, cover projections to eliminate FETCH stages, and verify with explain executionStats that docsExamined approaches nReturned.

Risk note

Every index taxes writes and storage. Index for observed query shapes from the profiler, not for imagined ones.

Verify the fix

Re-run explain: IXSCAN with no blocking sort, docsExamined within a small factor of nReturned, and profiler entries no longer flag the query.

Symptom walkthrough

The executionStats tell the story numerically:

> db.orders.find({customerId: 42, status: "paid"})
    .sort({createdAt: -1}).limit(50).explain("executionStats")

winningPlan: { stage: "COLLSCAN", filter: {...}, direction: "forward" }
executionStats: { nReturned: 50, docsExamined: 4210773, totalKeysExamined: 0 }

Fifty documents returned, four million examined — the selectivity gap is the entire cost. The zero in totalKeysExamined confirms no index participated. Also read the shape after fixing: SCAN_AND_SORT as a child of IXSCAN means the sort runs in memory (blocking, 32MB cap) because the index does not carry the sort order.

Fix in practice

Build the index in ESR order for this query shape:

// Equality (customerId, status) -> Sort (createdAt) -> Range (none)
db.orders.createIndex({ customerId: 1, status: 1, createdAt: -1 })

Cover the projection to skip FETCH entirely:

db.orders.find(
  { customerId: 42, status: "paid" },
  { _id: 0, createdAt: 1, total: 1 }   // fields present in the index
).sort({ createdAt: -1 })
// plan: PROJECTION_COVERED over IXSCAN — zero document fetches

Serve the shapes the profiler actually shows — index each distinct hot shape, and drop indexes that duplicate another prefix, since each one taxes every write.