How the Query Planner Chooses a Plan
When MongoDB receives a query, the query planner generates several candidate execution plans, one per usable index (plus a collection scan as a fallback), and runs them in a brief competition against a small subset of documents. The plan that returns the requested number of results with the fewest work units (measured by 'works', roughly documents examined plus index keys scanned) wins and its winning plan is cached for that query shape, so future identical-shaped queries skip the competition and reuse the cached plan directly.
Cricket analogy: The planner's trial run is like a franchise trialing three different opening pairs in practice matches before the season and locking in whichever pair scores fastest, then sticking with that lineup for subsequent matches of the same format.
Reading explain() Output
Calling db.collection.find(query).explain('executionStats') returns a detailed plan tree. The most important fields are winningPlan.stage (IXSCAN means an index was used, COLLSCAN means a full scan), totalDocsExamined (documents fetched from disk), totalKeysExamined (index entries scanned), and nReturned (documents actually matching). A healthy indexed query has totalKeysExamined and totalDocsExamined close to nReturned; a large gap between examined counts and returned counts signals an inefficient index or missing index entirely.
Cricket analogy: A winningPlan.stage of COLLSCAN when searching for a specific player's wickets is like a statistician manually flipping through every ball bowled in a season instead of consulting a pre-built 'wickets by bowler' index card.
db.orders.find({ status: "completed", totalAmount: { $gt: 100 } })
.sort({ createdAt: -1 })
.explain("executionStats");
/* Example relevant excerpt:
{
"executionStats": {
"nReturned": 42,
"totalKeysExamined": 42,
"totalDocsExamined": 42,
"executionStages": {
"stage": "FETCH",
"inputStage": {
"stage": "IXSCAN",
"indexName": "status_1_createdAt_-1_totalAmount_1"
}
}
}
}
*/Run explain('executionStats'), not just explain() with no argument (which only returns queryPlanner info without actually executing the query), when you need real totalDocsExamined and nReturned counts to diagnose performance.
The Plan Cache
MongoDB caches the winning plan per query shape (the combination of filter predicates, sort, and projection, ignoring literal values) so that repeated queries with the same shape skip the planning competition. The cache can become stale as data distribution changes, and it is automatically evicted when indexes are added or dropped, the mongod restarts, or after enough writes occur. You can inspect cached plans with db.collection.getPlanCache().list() and manually clear a specific shape's cache entry with clearPlansByQuery() if you suspect a stale plan is causing regressions after a data shift.
Cricket analogy: A cached query plan for 'find by team' is like a broadcaster reusing yesterday's graphics template for today's team stats, only regenerating it if the team roster structure itself changes.
A stale cached plan chosen for an old data distribution can occasionally be suboptimal for new data patterns, causing sudden performance regressions with no code change — if this is suspected, use getPlanCache().clear() and re-run explain() to confirm a better plan is now chosen.
- The query planner runs a short competition among candidate index plans and a COLLSCAN fallback, picking the plan with fewest 'works'.
- explain('executionStats') exposes winningPlan.stage, totalDocsExamined, totalKeysExamined, and nReturned.
- IXSCAN means an index was used; COLLSCAN means a full collection scan occurred.
- A large gap between examined counts and nReturned signals a missing or poorly targeted index.
- Winning plans are cached per query shape and reused until invalidated by index changes, restarts, or enough writes.
- getPlanCache().list() inspects cached plans; clearPlansByQuery() or clear() removes stale entries.
- Always pass 'executionStats' (or 'allPlansExecution') to explain() to get real execution numbers, not just the plan shape.
Practice what you learned
1. What does a winningPlan.stage of COLLSCAN indicate?
2. Which explain() mode is needed to see actual totalDocsExamined and nReturned values?
3. What is a 'query shape' in the context of the plan cache?
4. What typically invalidates a cached query plan?
5. In a healthy indexed query, how should totalKeysExamined compare to nReturned?
Was this page helpful?
You May Also Like
Indexes in MongoDB
Learn how MongoDB indexes work internally, why they speed up queries, and how to create and manage the core index types.
Compound Indexes
Understand how MongoDB compound indexes combine multiple fields, the ESR rule for ordering keys, and how they support sorting and covered queries.
Performance Tuning Basics
Practical techniques for diagnosing and fixing slow MongoDB queries, covering the profiler, index hygiene, schema design, and working-set memory.