Explain plan analysis (queryPlanner, executionStats) in MongoDB - Step-by-Step Execution

Concept Flow - Explain plan analysis (queryPlanner, executionStats)

Run explain() on query

↓

queryPlanner analyzes query

↓

queryPlanner outputs plan details

↓

executionStats collects runtime info

↓

executionStats outputs execution metrics

↓

User reviews plan and stats for optimization

The explain() command first analyzes the query plan, then runs the query to collect execution stats, helping users understand and optimize query performance.

Execution Sample

MongoDB

db.collection.find({age: {$gt: 30}}).explain('executionStats')

This command shows the query plan and execution statistics for finding documents where age is greater than 30.

Execution Table

Step	queryPlanner Stage	Details	executionStats Metrics	Notes
1	COLLSCAN	Collection scan on 'collection'	nReturned: 5, totalKeysExamined: 0, totalDocsExamined: 100	Full scan because no index on 'age'
2	Filter	Filter documents where age > 30	nReturned: 5	Filter applied after scanning all docs
3	Execution Complete	Query finished	executionTimeMillis: 15	Total time to run query

💡 Query completes after scanning all documents and filtering results

Variable Tracker

Variable	Start	After Step 1	After Step 2	Final
nReturned	0	5	5	5
totalDocsExamined	0	100	100	100
executionTimeMillis	0	N/A	N/A	15

Key Moments - 3 Insights

Why does the queryPlanner show a COLLSCAN instead of using an index?

Because there is no index on the 'age' field, the queryPlanner chooses a collection scan, as shown in execution_table row 1.

What does nReturned represent in executionStats?

nReturned shows how many documents matched and were returned by the query, as seen increasing from 0 to 5 in execution_table rows 2 and 3.

Why is totalDocsExamined higher than nReturned?

Because the query scanned 100 documents but only 5 matched the filter condition, as shown in execution_table row 1 and 2.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of nReturned after the Filter stage?

A 100

B 5

C 0

D 15

💡 Hint

Check the 'executionStats Metrics' column in row 2 labeled 'Filter'

At which step does the queryPlanner decide to scan the entire collection?

A Step 1

B Step 3

C Step 2

D None

💡 Hint

Look at the 'queryPlanner Stage' column in the first row

If an index on 'age' existed, how would the executionStats likely change?

A executionTimeMillis would increase

B nReturned would increase

C totalDocsExamined would decrease

D COLLSCAN would still be used

💡 Hint

Refer to variable_tracker for totalDocsExamined and consider how indexes reduce scanned documents

Concept Snapshot

Use db.collection.find(query).explain('executionStats') to see how MongoDB plans and runs a query.
queryPlanner shows the chosen plan (e.g., COLLSCAN or IXSCAN).
executionStats shows runtime info like documents scanned and returned.
Compare nReturned and totalDocsExamined to understand efficiency.
Use this info to add indexes and optimize queries.

Full Transcript

Explain plan analysis in MongoDB involves running the explain() method with 'executionStats' mode. This shows two main parts: the queryPlanner and the executionStats. The queryPlanner decides how MongoDB will run the query, for example by scanning the whole collection or using an index. The executionStats then shows what actually happened when the query ran, like how many documents were scanned and how many matched. By looking at these details, users can understand if their query is efficient or if they need to add indexes to speed it up. For example, if the queryPlanner shows a collection scan and executionStats shows many documents examined but few returned, it means the query is scanning too much data. Adding an index on the queried field can reduce the documents scanned and improve performance.