Query Planning

What we're doing: Show two candidate plans (two different indexes) being evaluated, and the trial-based reasoning behind the winner.

// Two indexes exist on this collection: { status: 1 } and { status: 1, createdAt: -1 }
db.orders.find({ status: "pending" }).sort({ createdAt: -1 })

// The planner tries both as candidates in a short trial:
// - { status: 1 } needs an in-memory sort after fetching matches
// - { status: 1, createdAt: -1 } returns already-sorted results — wins the trial, gets cached

1

Both indexes could technically serve this query, which is exactly why the planner needs to choose between real candidates rather than there being one obvious answer.

5

The compound index avoids an extra in-memory sort step, so it produces the requested results with less work — that is what the trial period is measuring, not an abstract judgment about index design.

Why this works: Understanding that the planner discovers the winner empirically, rather than reasoning about it from index definitions alone, explains why adding or dropping an index can change a query's plan even when the query itself never changes.

What we're doing: Show the same query's stage name changing from COLLSCAN to IXSCAN after adding the relevant index.

// No index on email:
db.users.find({ email: "a@x.com" }).explain().queryPlanner.winningPlan.stage
// -> "COLLSCAN"

// After db.users.createIndex({ email: 1 }):
db.users.find({ email: "a@x.com" }).explain().queryPlanner.winningPlan.stage
// -> "FETCH" (with an "IXSCAN" inputStage underneath)

1

Without a usable index, the winning plan's top-level stage is COLLSCAN — there was no candidate index-based plan to compete with it.

4

With the index in place, the winning plan becomes FETCH (retrieving the matched documents) with IXSCAN nested underneath it — the two stages typically appear together, not IXSCAN alone.

Why this works: The stage tree, not a single stage name, is what tells the real story — FETCH-over-IXSCAN is the normal shape for an indexed equality query, and expecting to see IXSCAN as the sole top-level stage would miss this.

What we're doing: Trace a full stage tree for a query needing both an index scan and an in-memory sort, explaining what each layer contributes.

db.orders.find({ status: "pending" }).sort({ total: -1 })
// with only { status: 1 } indexed (not total):

// winningPlan.stage: "SORT"
//   inputStage.stage: "FETCH"
//     inputStage.stage: "IXSCAN" (on { status: 1 })

1

This query filters by status (indexed) but sorts by total (not indexed) — a shape that needs more than a single scan stage to satisfy.

5

Reading bottom-up: IXSCAN finds matching status entries, FETCH retrieves the full documents, and SORT — the root stage — orders them by total in memory, since no index provided that order already.

Why this works: This is the concrete evidence for the ESR ordering principle (compound-index-design section) — a compound index on { status: 1, total: -1 } would let SORT disappear from this tree entirely, because the index itself would already return status-filtered results in total order.

What we're doing: Use allPlansExecution to see the rejected candidates alongside the winner, not just the final choice.

db.orders.find({ status: "pending", region: "west" }).explain("allPlansExecution")

// .queryPlanner.winningPlan       -> the plan that won the trial
// .queryPlanner.rejectedPlans     -> the candidates that lost, and why (fewer results per unit of work)

1

"allPlansExecution" mode runs and reports on every candidate plan's trial performance, not just the eventual winner.

4

rejectedPlans shows what else was considered — useful for understanding why a particular index was NOT chosen, not only confirming which one was.

Why this works: Seeing the rejected candidates, not just the winner, is often what actually explains a surprising plan choice — an index that "should" have won sometimes shows up in rejectedPlans with a concrete, checkable reason it lost the trial.

What we're doing: Show the three verbosity levels applied to the same query, and what each adds over the previous one.

db.orders.find({ status: "pending" }).explain()
// -> plan only: which stage tree would run, no execution, no real numbers

db.orders.find({ status: "pending" }).explain("executionStats")
// -> actually runs it: adds nReturned, totalDocsExamined, executionTimeMillis for the winning plan

db.orders.find({ status: "pending" }).explain("allPlansExecution")
// -> adds the same real numbers for every candidate plan that was tried, not just the winner

1

The default level answers "what plan would run" without spending any execution time or touching real data.

5

"executionStats" is the level most performance investigation actually needs — real, measured numbers for the plan that actually won.

Why this works: Choosing the right verbosity level matters because executionStats and allPlansExecution actually run the query (or every candidate), which has a real cost on a large or slow query — reaching for allPlansExecution by default when queryPlanner or executionStats would answer the question is unnecessary extra work.

What we're doing: Compare a rejected plan's stats against the winner to see a genuinely correct rejection, not a surprising one.

db.orders.find({ status: "pending", region: "west" }).explain("allPlansExecution")

// winningPlan (index on { region, status }): examined 120 keys, returned 118 docs
// rejectedPlans[0] (index on { status }): examined 4,500 keys, returned 118 docs — same result, far more work

1

allPlansExecution is what makes this comparison possible in the first place — the default verbosity would only show the winner.

4

This rejection is genuinely correct: the { status }-only index had to examine 4,500 candidates to find the same 118 matches the { region, status } index found by examining only 120 — the trial measured a real, large difference.

Why this works: This is what "practical" means for rejected plans — not reading rejectedPlans as an error report, but as data that either confirms the winner deserved to win or points at a specific index that is not serving this query shape as well as it appears to on paper.

What we're doing: Show a poorly-aligned index producing a large scan-to-return ratio despite genuinely using IXSCAN.

// Index exists on { region: 1 } only — the query filters on region AND status:
db.orders.find({ region: "west", status: "cancelled" }).explain("executionStats").executionStats
// -> stage: "IXSCAN" (on region), totalKeysExamined: 40,000, nReturned: 60
// -> ratio: 40,000 / 60 ≈ 667 — the index narrowed by region, but examined every "west" order before filtering status in memory

1

The winning plan genuinely uses IXSCAN — this is not a case of falling back to COLLSCAN.

4

The scan-to-return ratio reveals the real story: the index only helps with region, and status filtering happens after fetching far more documents than the query actually needed.

Why this works: A stage-name check ("does it say IXSCAN") answers a different, easier question than a ratio check ("is this index actually earning its keep for this query") — both matter, but only the second one predicts whether a query is actually fast.