How to read a MongoDB explain plan

What explain output actually contains

When you run db.collection.find({…}).explain("executionStats"), MongoDB returns a nested document describing the winning plan: the execution strategy the query planner chose from all the candidates it considered. Useful fields across the explain document include:

• stage — what type of work this node performs (see below).
• nReturned — documents passed upward to the parent stage.
• totalDocsExamined — documents the engine actually read from disk or memory. A large gap between this and nReturned signals a poor index or a missing index.
• totalKeysExamined — index keys scanned. Should be close to nReturned for a well-covered query.
• executionTimeMillis — wall-clock time for the entire plan.

The most important stage types

MongoDB plans are trees of named stages. Understanding the stage names lets you diagnose problems at a glance:

• COLLSCAN — a full collection scan. Every document is read and checked against the filter. This is fine for small collections but becomes expensive as data grows. If you see COLLSCAN on a large collection, you are missing an index.
• IXSCAN — an index scan. MongoDB walks the B-tree for the named index. This is what you want. Check indexName to confirm it is the index you expected.
• FETCH — after an IXSCAN, MongoDB fetches the full document from the collection to satisfy fields not covered by the index. If your query only needs indexed fields, a covered query (no FETCH stage) is even faster.
• SORT — an in-memory sort. Appears when no index covers the sort order. Large in-memory sorts can hit MongoDB's 100 MB sort memory limit and fail. Adding a compound index that covers both the filter and the sort fields eliminates this stage.
• LIMIT / SKIP — applied after earlier stages. Seeing SKIP high in a plan that also has COLLSCAN is a common pagination anti-pattern.
• OR / AND_SORTED / AND_HASH — multi-index merge plans. These appear when MongoDB combines results from multiple indexes. They work, but a single well-designed compound index usually outperforms them.

The golden ratio: keys examined vs docs returned

A healthy query has totalKeysExamined ≈ nReturned andtotalDocsExamined ≈ nReturned. If the engine examines 50,000 keys to return 10 documents, the index selectivity is low and you should consider a more specific compound index or a different field order.

The index ratio — nReturned / totalKeysExamined — is a quick health metric. Ratios below 0.1 (less than 10% efficiency) almost always warrant an index redesign.

Explain plans for aggregation pipelines

Aggregation pipelines produce explain output too, but the structure is different. The planner emits a stages array where each element corresponds to a pipeline stage, and the first stage typically contains an inner queryPlanner that describes the initial document selection. Look for:

• Whether the $match at the start of the pipeline uses an IXSCAN. If it falls back to COLLSCAN, move the $match earlier or add an index on the matched field.
• $sort stages that appear without a backing IXSCAN — these cause in-memory sorts and should be resolved with a compound index that covers both the match predicate and the sort fields.
• $lookup stages that perform a full scan of the joined collection. Add an index on the foreignField of every $lookup.

Reading explain plans visually in MQLens

Raw explain JSON can be hundreds of lines deep. MQLens renders the plan as a visual tree showing stage names and index details. Switch toRaw JSON to inspect document counts, keys examined, and execution timing reported by MongoDB.

To get an explain plan in MQLens:

1. Open a collection and write your find filter in the query panel.
2. Open the dropdown beside Run and choose Run Explain. MQLens runs the query with executionStats verbosity.
3. The results area opens the Explain Plan tab. Switch between Visual Tree and Raw JSON.
4. For aggregation pipelines, open the pipeline builder, compose your stages, and choose Run Explain from the same dropdown. The visual tree shows pipeline stages and the available query plan.

Use the tree to follow the plan structure, then inspect the raw output for detailed statistics and nested lookup plans.