MongoDB explain() Plan Explained: How to Read It

MongoDB explain() Plan Explained: How to Read It

explain() tells you how MongoDB runs a query. Here is how to read it.

How to Run explain()

User.find({ email: '[email protected]' }).explain('executionStats');

Three modes:

• 'queryPlanner': just the plan (no execution). Fast.
• 'executionStats': runs the query and includes stats. Slower but more useful.
• 'allPlansExecution': stats for all candidate plans. Slowest.

Use 'executionStats' for optimization.

Key Fields

winningPlan.stage

• COLLSCAN: collection scan. Bad. MongoDB reads every document.
• IXSCAN: index scan. Good. MongoDB uses an index.
• FETCH: fetches the full document after an index scan. Normal.
• SORT: in-memory sort. Can be slow for large result sets.
• LIMIT: applies a limit. Normal.

executionStats.totalDocsExamined

Number of documents MongoDB read. Should be close to the number of documents returned. If it equals the collection size, you are doing a collection scan (bad).

executionStats.totalKeysExamined

Number of index entries scanned. Should be close to the number of documents returned. If it is much larger, the index is not selective enough.

executionStats.executionTimeMillis

How long the query took. Useful for comparison.

Example: Bad Query (No Index)

{
  winningPlan: { stage: 'COLLSCAN' },
  executionStats: { totalDocsExamined: 100000, totalKeysExamined: 0 }
}

MongoDB scanned all 100000 documents. Add an index on the filter field.

Example: Good Query (With Index)

{
  winningPlan: { stage: 'FETCH', inputStage: { stage: 'IXSCAN', keyPattern: { email: 1 } } },
  executionStats: { totalDocsExamined: 1, totalKeysExamined: 1 }
}

MongoDB used the email index, scanned 1 index entry, fetched 1 document. Optimal.

Sort Stage

If you see a SORT stage, MongoDB is sorting in memory. For large result sets, this is slow. Add an index that matches your sort order to remove the SORT stage.

Covered Query

If all the fields you need are in the index, MongoDB can return results from the index without fetching the document. The plan shows a PROJECTION_COVERED stage. Fastest.

The Takeaway

Read explain() by looking at winningPlan.stage (COLLSCAN bad, IXSCAN good), executionStats.totalDocsExamined (should be small), and executionStats.totalKeysExamined (should be small). Add an index if you see COLLSCAN or a large totalDocsExamined. Add a sort index if you see a SORT stage.