Reading explain() plans like a DBA

conceptThe concept

• explain("queryPlanner") (default): the chosen plan and rejected plans, without executing.
• explain("executionStats"): runs the winning plan and reports counts and timings per stage.
• explain("allPlansExecution"): also shows the partial trial-period statistics of every candidate.

Stages to recognise: IXSCAN (with indexBounds), FETCH (load documents, optionally applying a filter), SORT (in-memory, blocking), LIMIT/SKIP, PROJECTION_COVERED (answered from the index alone), OR/SUBPLAN (separate plans per $or branch), COLLSCAN.

When several indexes could answer a query, the planner runs each candidate for a short trial period (until one returns 101 documents or does 10,000 "works") and picks the most productive. The winner is cached in the plan cache by query shape.

whyWhy it matters

The difference between a 3 ms and a 3 s query is usually visible in explain: a filter applied in FETCH instead of index bounds, a SORT stage that should have been avoided, or the wrong index winning the trial. Reading plans is the core performance skill for developers and DBAs.

Use caseReal-world scenario

An API sorting orders by date became slow. explain shows IXSCAN on { status: 1 } followed by a SORT on 1,000 documents. Adding { status: 1, createdAt: -1 } removes the SORT stage and the query examines only 20 keys.

Try itExamples

Run and edit these in the interactive lesson.

db.orders.createIndex({ status: 1 })
db.orders.find({ status: "paid" }).sort({ createdAt: -1 }).limit(10).hint({ status: 1 }).explain("executionStats")

db.orders.createIndex({ status: 1, createdAt: -1 })
db.orders.find({ status: "paid" }).sort({ createdAt: -1 }).limit(10).explain("executionStats")

db.orders.createIndex({ customerId: 1 })
db.orders.find({ status: "paid", customerId: 50 }).explain("allPlansExecution")
db.orders.find({ status: "paid", customerId: 50 }).hint({ status: 1 }).explain("executionStats")

internalsUnder the hood

indexBounds show exactly which part of the index is scanned: [ "paid", "paid" ] is a point, [ 100, inf ] a range, [ MinKey, MaxKey ] the whole index. The plan cache entry for a shape is evicted when indexes change, when the collection changes significantly, or on restart; a cached plan that performs much worse than expected is replanned automatically. queryHash/planCacheKey identify the shape in logs and in $planCacheStats.

Avoid / preferDo this, not that

db.orders.find({ status: "paid" }).explain()

db.orders.find({ status: "paid" }).explain("executionStats")

// "it ran in 3 ms on my laptop, ship it"

const e = db.orders.find({ status: "paid" }).explain("executionStats").executionStats
printjson({ keys: e.totalKeysExamined, docs: e.totalDocsExamined, returned: e.nReturned })

PitfallsCommon mistakes

• Reading only executionTimeMillis on a warm cache; examined counts are the stable signal.
• Leaving hint() in application code after data distribution changes.
• Ignoring rejectedPlans: too many candidate indexes make planning slower and choices unstable.

LimitsWhen not to use it

explain("executionStats") and "allPlansExecution" actually run the query (writes are not applied). Do not run them against a heavy query on a busy primary just to look; use "queryPlanner" first, or a secondary.

SecuritySecurity notes

Explain output reveals collection and index names, field names and literal values in index bounds. Keep it in logs and dashboards for engineers; never return it to API clients.

ChallengeTry it yourself

db.orders.createIndex({ status: 1 })
const a = db.orders.find({ status: "returned" }).explain("executionStats").executionStats
const b = db.orders.find({ status: "returned" }).hint({ _id: 1 }).explain("executionStats").executionStats
printjson({ withIndex: a.totalDocsExamined, forcedId: b.totalDocsExamined, returned: a.nReturned })

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.