Lesson 0004 · The planner
You can read EXPLAIN in your sleep. Mongo's explain() looks familiar — a tree of stages — but the planner underneath doesn't cost plans from statistics the way Postgres does. It races them and keeps the observed winner.
For find({ status: "active", age: {$gt:21} }).sort({ name: 1 }), the best index is:
Index { a: 1, b: 1, c: 1 }. Which query can it serve as a prefix?
A multikey (array) index and a covered query:
Here is a skill that transfers almost whole. In Postgres 0006
and MySQL 0003 you learned to
read a plan as a tree of stages, bottom-up, and to hunt the one number that
matters most. Mongo's explain() is the same tree in different clothes. But
one thing underneath is genuinely different, and it's the keystone of this lesson:
Postgres and MySQL estimate a plan's cost from table statistics and pick
the cheapest before running it. MongoDB's classic planner instead runs the
candidate plans against each other and keeps whichever actually wins.
explain() takes a verbosity. queryPlanner
(the default) shows the plan the optimizer would use, without running it.
executionStats actually runs the query and reports what
happened. allPlansExecution adds the trial-run data for the
losing plans too.
— MongoDB Manual: to include executionStats "you must run the explain in either: executionStats or allPlansExecution verbosity mode"; allPlansExecution includes "partial execution data captured during plan selection."
For real tuning you almost always want executionStats — the plan alone can
lie; the measured run cannot. (Unlike Postgres, this is not a DML hazard: an
explain of a write does not apply it.)
The plan lives in winningPlan, with the losers in rejectedPlans.
— MongoDB Manual: winningPlan = "the plan selected by the query optimizer"; rejectedPlans = "candidate plans considered and rejected by the query optimizer."
Each node is a stage, and the two you'll read most are the two you already
know by other names:
— MongoDB Manual: stages include COLLSCAN "collection scan," IXSCAN "scanning index keys," FETCH "retrieving documents."
Notice how 0003 pays off: an IXSCAN with no FETCH is exactly a
covered query; a lone SORT stage means your index didn't
supply the order and MongoDB paid for an in-memory sort — the thing an ESR index exists
to remove.
This is where MongoDB parts ways with everything in your SQL courses. When the shape is new, the optimizer builds the candidate plans and runs them side by side for a short trial period; the winner is the plan that produces the most results while doing the least work during that trial. — MongoDB Manual: "In the classic multi-planner, the winning plan is the query plan that produces the most results during the trial period while performing the least amount of work." (MongoDB 8.3+ adds a cost-based ranker as a backup when the trial can't decide. — MongoDB Manual: "Starting in MongoDB 8.3, multi-planning with a cost-based ranker backup is the default plan selection mechanism… CBR evaluates each node in a plan based on a cost function and its cardinality estimations.")
Postgres's planner never runs your query to choose — it multiplies row estimates by per-operation costs and picks the cheapest on paper (which is exactly why stale statistics were the villain in Postgres 0008). MongoDB's classic planner sidesteps estimation error by empirically racing the plans — but it pays a different tax: the winner is cached by query shape and reused, so a plan chosen on an unrepresentative trial can stick around.
Just as Postgres's key tell was estimated vs actual rows, MongoDB's is
keys/documents examined vs documents returned. In
executionStats three numbers matter: nReturned (docs returned),
totalKeysExamined (index entries walked), and totalDocsExamined
(documents opened).
— MongoDB Manual: nReturned "Number of documents returned"; totalKeysExamined "Number of index entries scanned"; totalDocsExamined "Number of documents examined… not… the number of documents returned."
The health check is a ratio: examined ≈ returned (near 1:1) is efficient;
a big gap means you're reading piles of data to emit a few rows.
— MongoDB Manual: when "keys examined match the number of documents returned… a very efficient query"; an unindexed query shows totalKeysExamined: 0 with totalDocsExamined = whole collection.
The winning plan is stored in the query plan cache, keyed by
plan cache query shape, and reused for later queries of the same shape.
— MongoDB Manual: "Both ranking mechanisms store the chosen plan in the query plan cache and reuse it for subsequent queries with the same plan cache query shape."
It clears itself when the ground shifts: any DDL event — creating,
dropping, or hiding an index — flushes the cache for that collection, and it also drops
entries by LRU and does not survive a restart.
— MongoDB Manual: "Any DDL event clears the plan cache for the relevant collection… dropping a collection, and creating, deleting, or hiding an index." Also LRU eviction, and "does not persist if a mongod restarts."
Two practical corollaries: after you add an index, the next query re-plans (good — it can
discover your new index); and explain() deliberately ignores the
cache and won't create an entry, so it always shows a fresh planning decision.
— MongoDB Manual: "Using explain ignores all existing plan cache entries and prevents the MongoDB query planner from creating a new plan cache entry."
| Question | Postgres / MySQL EXPLAIN | MongoDB explain() |
|---|---|---|
| Choose a plan by… | Estimating cost from statistics | Racing candidate plans (classic) |
| #1 tell | Estimated vs actual rows | Examined vs returned |
| See real numbers with… | EXPLAIN ANALYZE | explain("executionStats") |
| Running a write plan | ANALYZE executes it (danger) | Safe — explain doesn't apply it |
| Full scan is called | Seq Scan / full scan | COLLSCAN |
| Reused plan cache | Prepared-statement generic plan | Cache keyed by query shape |
Answer from memory — the effortful recall is what builds retention. Feedback is immediate.
How does MongoDB's classic planner pick the winning plan?
Which single comparison best tells you a query is efficient?
In a plan, an IXSCAN with no FETCH stage means:
You create a new index. What happens to that collection's plan cache?
Which verbosity actually runs the query and reports real counts?
No Mongo handy yet, so this is a lab for later (Docker:
docker run --rm -p 27017:27017 mongo, then mongosh):
db.people.find({ status: "active", age: { $gt: 21 } })
.sort({ name: 1 }).explain("executionStats") // read stage + the 3 counts
// Before any index: expect COLLSCAN, totalKeysExamined 0, totalDocsExamined = N.
db.people.createIndex({ status: 1, name: 1, age: 1 }) // the ESR index
// Re-run explain: expect IXSCAN, no SORT stage, examined ≈ nReturned.
db.people.getPlanCache().list() // see cached shapes + statesThe win to watch: the SORT stage disappears and
totalDocsExamined collapses toward nReturned. Bring the two
plans (before/after) to your teacher and we'll read them side by side.
MongoDB Manual — Analyze Query Performance is the practical one-pager (COLLSCAN vs IXSCAN, the examined:returned ratio). Then Explain Results for every field, and Query Plans & the Plan Cache for how the winner is chosen and cached.