Mastering MongoDB Explain Plans: Optimize Queries with Index Strategies

MongoDB Explain Plan Analysis

MongoDB has become a hot document‑oriented database, yet Chinese resources on its internals are scarce. This series shares practical tips on tuning and troubleshooting, focusing on query execution plan analysis.

Explain Modes (MongoDB 3.0+)

After version 3.0, explain supports three modes:

queryPlanner (default)

executionStats

allPlansExecution

queryPlanner

The default mode analyzes the query without actually executing it and selects a winning plan. Key fields include:

explain.queryPlanner.namespace : the collection being queried.

explain.queryPlanner.indexFilterSet : whether an index filter is applied.

explain.queryPlanner.winningPlan : details of the optimal plan.

explain.queryPlanner.winningPlan.stage : the final stage (e.g., FETCH).

explain.queryPlanner.winningPlan.inputStage : child stage, often IXSCAN.

explain.queryPlanner.winningPlan.keyPattern : index fields used.

explain.queryPlanner.winningPlan.indexName : name of the chosen index.

explain.queryPlanner.winningPlan.isMultiKey : whether the index is multikey.

explain.queryPlanner.winningPlan.direction : scan direction (forward/backward).

explain.queryPlanner.winningPlan.indexBounds : range of index keys scanned.

explain.queryPlanner.rejectedPlans : non‑winning plans.

executionStats

This mode adds detailed runtime statistics:

executionStats.executionSuccess : whether the query succeeded.

executionStats.nReturned : number of documents returned.

executionStats.executionTimeMillis : total execution time.

executionStats.totalKeysExamined : number of index entries scanned.

executionStats.totalDocsExamined : number of documents scanned.

executionStats.executionStages.stage : the outermost stage (e.g., FETCH).

executionStats.executionStages.nReturned and executionStats.executionStages.docsExamined : match the outer fields.

executionStats.inputStage : mirrors the inner stage information.

Additional low‑level fields such as works, advanced, and isEOF are initialized in the source files plan_stats.h, fetch.cpp, and eof.cpp.

IndexFilter

IndexFilters restrict which indexes the optimizer may consider for a given query shape. They are created, listed, and removed with commands shown below.

db.collection.createIndex({field:1})
// Create an index filter
db.runCommand({setParameter:1, indexFilter: {collection:"orders", filter:{status:1}}})

When an IndexFilter is present, a hint will be ignored and the optimizer will only evaluate the indexes defined in the filter.

Stage Types

Common stages include:

COLLSCAN – full collection scan

IXSCAN – index scan

FETCH – retrieve documents using index keys

SHARD_MERGE – merge results from shards

SORT – in‑memory sort (no supporting index)

LIMIT – limit result count

SKIP – skip a number of results

IDHACK – fast _id lookup

SHARDING_FILTER – filter shard key ranges

COUNT, COUNTSCAN, COUNT_SCAN – count operations

SUBPLA – $or without index

TEXT – full‑text search

PROJECTION – field projection

Practical Example

Dataset: 10 documents (illustrated below).

Query:

db.d.find({a:1,b:{$lt:3}}).sort({c:-1})

Without Index

The plan shows a COLLSCAN and an in‑memory SORT, scanning all 10 documents.

Adding Index on Sort Field

After db.d.ensureIndex({c:1}), the SORT stage disappears, but totalDocsExamined remains 10 because the query predicate still lacks an index.

Compound Indexes

Using db.d.ensureIndex({b:1,a:1,c:1}) yields nReturned=2, totalKeysExamined=4, totalDocsExamined=2, but a SORT stage still appears.

Using db.d.ensureIndex({a:1,b:1,c:1}) reduces totalKeysExamined to 2, matching nReturned, yet SORT remains.

Finally, creating db.d.ensureIndex({a:1,c:1,b:1}) eliminates the SORT stage. The plan scans 4 index entries and returns 2 documents, achieving index‑based sorting.

Conclusion: For queries that combine exact matches, range filters, and sorting, an index ordered as {exactMatchFields, sortField, rangeFields} yields the most efficient execution.

allPlansExecution

This mode runs executionStats on every possible plan, providing exhaustive statistics.

Conclusion

Understanding the fields returned by explain, leveraging IndexFilters, and designing compound indexes that align with query patterns can dramatically reduce index scans, document scans, and in‑memory sorts, leading to faster and more predictable MongoDB performance.