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. The two most important fields at each stage are:

• 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 and totalDocsExamined ≈ 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 an interactive tree: each node shows its stage name, nReturned, documents examined, and execution time. You can expand or collapse branches and immediately see which stage is responsible for the most document reads.

To get an explain plan in MQLens:

1. Open a collection and write your find filter, sort, and projection in the query panel.
2. Click the Explain button next to the Run button. MQLens runs the query with executionStats verbosity.
3. The visual plan tree opens in a side panel. COLLSCAN nodes are highlighted in amber; IXSCAN nodes appear in the accent color.
4. For aggregation pipelines, open the pipeline builder, compose your stages, and click Explain Pipeline. The tree expands to show each pipeline stage alongside its inner query plan node.

The tree view makes it immediately obvious when a deep nested COLLSCAN is hiding inside an otherwise indexed aggregation pipeline — something that is easy to miss in raw JSON output.