TopolearnSudden slowdowns often come from plan flips, and fixing them requires reading the planner's decisions.
For a query shape with several usable indexes, the planner generates candidate plans and races them in a trial period: each plan runs until one returns a batch of results (101 documents by default) or does enough work. The most productive plan wins and is stored in the plan cache keyed by the query shape (filter, sort and projection structure, not the constants).
Later queries with the same shape reuse the cached plan. Cache entries are evicted when the index set changes, on restart, or if the cached plan performs much worse than when it was cached, triggering a re-plan. This is why the same query can be fast for one constant and slow for another: a plan chosen for a selective value is reused for a non-selective one.
Diagnosing:
explain("allPlansExecution")shows every candidate and its trial statistics, andrejectedPlans,db.coll.getPlanCache().list()shows cached plans;.clear()resets them,- the slow query log and the profiler (
db.setProfilingLevel(1, { slowms: 100 })) recordplanSummary,keysExamined,docsExamined.
Steering: hint({ field: 1 }) forces an index for one query; index filters (planCacheSetFilter) restrict choices for a shape. The better fix is usually an index that clearly dominates (ESR) or removing redundant indexes that confuse the race. Modern versions run many queries in the slot-based execution engine (SBE), visible in explain as queryPlanner.winningPlan.slotBasedPlan.
Example
db.orders.createIndex({ status: 1 })
db.orders.createIndex({ createdAt: -1 })
const q = { status: "open", createdAt: { $gte: ISODate("2026-10-01") } }
const ex = db.orders.find(q).sort({ createdAt: -1 }).explain("allPlansExecution")
ex.queryPlanner.winningPlan // which index won the trial
ex.queryPlanner.rejectedPlans.length // 1
db.orders.getPlanCache().list().length // cached shapes for this collection
db.orders.find(q).sort({ createdAt: -1 }).hint({ status: 1 }) // force an index
db.setProfilingLevel(1, { slowms: 100 }) // log slow operations to system.profile
db.system.profile.find().sort({ ts: -1 }).limit(1)
// best fix here: one ESR index { status: 1, createdAt: -1 }Remember
- Candidate plans race in a trial; the winner is cached per query shape
- A cached plan may be poor for different constants: plan flips
- allPlansExecution, the plan cache and the profiler reveal decisions
- Prefer a dominating index over hints
Watch out for
- Sprinkling hint() in application code: it keeps forcing a plan even after data or indexes change.