beginnerintermediateadvanced

MongoDB · Query Planning

Query Planning

How the planner picks a plan, the execution stage tree, and using explain() to investigate it directly

Candidates run a trialmost results, least work wins — then caches
COLLSCAN vs. IXSCANthe literal explain() stage names
Stages nest bottom-upIXSCAN alone can still hide a SORT above it
IXSCAN ≠ well-alignedcheck totalKeysExamined ÷ nReturned

1.What the query planner does

Before running a query, MongoDB's query planner considers every index that could help, runs the plausible candidates for a short trial, and picks whichever returned the most results for the least work. That choice is then cached and reused for future queries of the same shape.

Key features

The planner generates candidate plans from every index that could apply to the query
Candidates run in parallel for a short trial period; the plan producing the most results for the least work wins
The winning plan is cached (in the query plan cache) and reused for future queries with the same shape
MongoDB 8.3+ adds a Cost-Based Ranker as a backup mechanism, using cost/cardinality estimation rather than a trial run
explain() bypasses the cache — it always evaluates plans fresh and does not create or consume a cache entry

From candidates to a cached winner

candidate plans

one per usable index

trial run

most results, least work

winning plan

cached for reuse

Command

text

db.<collection>.find({...}).explain("executionStats")   // shows the winning plan, bypassing the cache

The planner runs plausible candidate plans in a short trial, picks the one returning the most results for the least work, and caches that winner for future queries of the same shape. explain() always bypasses the cache.

2.COLLSCAN vs. IXSCAN

These are the two stage names explain() shows for how MongoDB locates candidate documents. COLLSCAN checks every document in the collection in turn. IXSCAN walks a sorted index structure, examining only the entries that could match.

Key features

COLLSCAN: the winning plan checks every document — no usable index for this query
IXSCAN: the winning plan walks an index, only examining entries that could match
Both appear as a stage name in explain()'s winningPlan tree, often nested under other stages like FETCH
COLLSCAN is not always wrong — a query returning most of a small collection may not benefit from an index at all
The presence of IXSCAN alone does not guarantee efficiency — see totalKeysExamined vs. nReturned in explain() analysis

Command

text

db.<collection>.find({...}).explain("executionStats").executionStats.executionStages.stage

COLLSCAN and IXSCAN are the literal explain() stage names for "checked every document" vs. "walked an index." They usually appear nested (FETCH over IXSCAN) — check the whole stage tree, not just the top-level stage.

3.FETCH, SORT, and other execution stages

Beyond COLLSCAN/IXSCAN: FETCH retrieves full documents after an index scan locates their positions, and SORT orders results in memory when no index provides the needed order. Stages nest in a tree, each one processing its child stage's output.

Key features

FETCH: retrieves the actual document for each index entry found — appears whenever fields beyond the index are needed
SORT: orders results in memory — appears when no index provides the query's requested order already
A SORT stage can spill to disk for large result sets, tracked via spills/spilledBytes in explain()
Stages nest via inputStage (one child) or inputStages (multiple, e.g. for an $or across indexes)
A query using only fields present in the index (a covered query) can skip FETCH entirely

Common stages and what triggers them

Common stages and what triggers them
Stage	Job	Appears when
COLLSCAN	checks every document	no usable index for this query
IXSCAN	walks an index	a usable index exists
FETCH	retrieves full documents	the query needs fields beyond what the index alone provides
SORT	orders results in memory	no index provides the requested sort order already

Command

text

// Read the stage tree bottom-up: leaf stages access data, parent stages process what their child returned

FETCH retrieves full documents after a scan; SORT orders results in memory when no index provides the needed order. Read the stage tree bottom-up — IXSCAN alone does not mean the whole query is optimal.

4.How a winning plan is selected among candidates

A candidate plan is one possible way to answer the query, generated per usable index. MongoDB's classic multi-planner runs the plausible candidates in parallel for a short trial and picks whichever produced the most results for the least work — that becomes the winning plan, cached for reuse.

Key features

A candidate plan corresponds to one usable index (or a COLLSCAN, if no index applies)
Candidates run in parallel for a short trial period, not to full completion
The winner is whichever candidate returned the most results for the least work during that trial
MongoDB 8.3+ adds a Cost-Based Ranker as a backup selection mechanism using cost/cardinality estimates rather than a trial run
A won plan is specific to a query shape — a different filter/sort combination runs its own separate selection

Command

text

db.<collection>.find({...}).explain("allPlansExecution")  // shows candidate plans, not just the winner

A candidate plan exists per usable index; the classic planner runs candidates in a short trial and picks the one with the best results-per-work ratio. Use explain("allPlansExecution") to see rejected candidates, not just the winner.

5.Using explain() to investigate a query

explain() runs the query planner (and optionally the query itself) and reports what it did instead of returning documents: which plan won, its stage tree, and — with "executionStats" — real numbers like how many documents were examined versus returned. Section 15 covers reading those numbers in depth; this is the tool itself.

Key features

db.collection.find({...}).explain() — default is "queryPlanner" verbosity, no execution
explain("executionStats") actually runs the query and reports real numbers (nReturned, totalDocsExamined, executionTimeMillis)
explain("allPlansExecution") runs every candidate plan's trial, not just the winner
explain() always evaluates fresh — it never reads from or writes to the query plan cache
Works on find(), aggregate(), and other commands — not just simple queries

Three verbosity levels, three trade-offs

queryPlanner

plan only, no execution

executionStats

runs it, real numbers

allPlansExecution

every candidate, not just the winner

Command

text

db.<collection>.find({...}).explain("executionStats")

explain() default: plan only, no execution. "executionStats": actually runs it, real numbers. "allPlansExecution": every candidate, not just the winner. Always bypasses the plan cache.

6.Reading rejected plans in practice

When a plan you expected to win instead shows up under rejectedPlans, the practical response is to compare its execution stats against the actual winner — often the winner genuinely did less work for this query shape, and the "expected" plan was a reasonable guess that the trial disproved.

Key features

rejectedPlans is only populated by explain("allPlansExecution") — the default and executionStats levels omit it
A rejected plan's stats (docsExamined, etc. from its own trial) are directly comparable to the winner's
A plan can be "correctly" rejected (winner really was better for this shape) or "surprisingly" rejected (worth a closer look)
A repeatedly-surprising rejection across many real queries can indicate an index that is not actually well-suited, despite looking right on paper
Query Settings (MongoDB 8.0+) can now influence which indexes the planner considers, replacing the deprecated index filters mechanism

Command

text

db.<collection>.find({...}).explain("allPlansExecution").queryPlanner.rejectedPlans

rejectedPlans (from allPlansExecution) shows losing candidates' real stats — compare them to the winner's before assuming a rejection means something is wrong. Most rejections are the trial correctly finding a better option.

7.Using an index does not guarantee a fast query

IXSCAN in the plan just means MongoDB walked an index — it says nothing about how well that index matches the query. An index examining thousands of entries to return a handful of results is technically "using an index" while still doing almost as much work as a scan.

Key features

IXSCAN confirms an index was used — it does not confirm the index fits the query well
The scan-to-return ratio (totalKeysExamined ÷ nReturned) is the concrete measure of alignment
A poorly-aligned index (wrong field order, missing a filter field) can examine nearly as many entries as a full scan
A well-aligned index examines close to nReturned entries — ideally a ratio near 1
This is why compound index design (ESR, prefix rule) is a real skill, not a checkbox — "an index exists" is not the goal, "the right index" is

IXSCAN is not the finish line

Query runs

region + status filter

IXSCAN on region

examines 40,000 keys

Check the ratio

totalKeysExamined ÷ nReturned

Ratio ≈ 667

misaligned, despite using an index

Command

text

// Compare totalKeysExamined to nReturned in explain("executionStats") — do not stop at "the stage says IXSCAN"

IXSCAN in the plan only confirms an index was used, not that it fits the query well. Compare totalKeysExamined to nReturned — a ratio far from 1 means a poorly-aligned index despite technically "using" one.

Spotted a mistake on this page? Tell us.

Read Sheet