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.

Practice

1. What does the queryPlanner section in MongoDB's explain output show?

easy

A. The plan MongoDB chooses to execute the query

B. The total time taken to run the query

C. The number of documents returned by the query

D. The syntax errors in the query

Solution

1. Step 1: Understand the role of queryPlanner

   The queryPlanner shows the strategy MongoDB picks to run the query, like which indexes it uses.

2. Step 2: Differentiate from executionStats

   executionStats shows runtime details, not the plan itself.

3. Final Answer:

   The plan MongoDB chooses to execute the query -> Option A

4. Quick Check:

   queryPlanner = plan chosen [OK]

Hint: queryPlanner shows the plan, executionStats shows runtime [OK]

Common Mistakes:

• Confusing queryPlanner with executionStats
• Thinking queryPlanner shows query results
• Assuming queryPlanner shows errors

2. Which of the following is the correct way to get execution stats for a query in MongoDB?

easy

A. db.collection.find({}).explain('runStats')

B. db.collection.find({}).explain('queryPlan')

C. db.collection.find({}).explain('statsExecution')

D. db.collection.find({}).explain('executionStats')

Solution

1. Step 1: Recall the correct explain verbosity

   MongoDB uses 'executionStats' to show runtime details.

2. Step 2: Check option syntax

   Only db.collection.find({}).explain('executionStats') uses the correct string 'executionStats' with explain().

3. Final Answer:

   db.collection.find({}).explain('executionStats') -> Option D

4. Quick Check:

   Use 'executionStats' for runtime info [OK]

Hint: Use 'executionStats' string inside explain() for runtime stats [OK]

Common Mistakes:

• Using wrong strings like 'queryPlan' or 'runStats'
• Forgetting quotes around verbosity string
• Confusing method chaining order

3. Given this explain output snippet:

{
  "executionStats": {
    "nReturned": 5,
    "totalDocsExamined": 100,
    "executionTimeMillis": 12
  }
}

What does totalDocsExamined tell you?

medium

A. Number of documents returned by the query

B. Number of documents scanned to find matches

C. Total documents in the collection

D. Number of indexes used

Solution

1. Step 1: Understand totalDocsExamined meaning

   This value shows how many documents MongoDB looked at to find matching results.

2. Step 2: Differentiate from nReturned

   nReturned is how many documents matched and were returned, not scanned.

3. Final Answer:

   Number of documents scanned to find matches -> Option B

4. Quick Check:

   totalDocsExamined = docs scanned [OK]

Hint: totalDocsExamined counts scanned docs, not returned [OK]

Common Mistakes:

• Confusing scanned docs with returned docs
• Thinking it counts indexes used
• Assuming it is total collection size

4. You run db.users.find({age: 30}).explain('executionStat') but get an error. Which is the likely cause?

medium

A. The verbosity string 'executionStats' is misspelled

B. Explain does not support 'executionStats' verbosity

C. The query filter syntax is invalid

D. The collection name is incorrect

Solution

1. Step 1: Check common explain errors

   Misspelling the verbosity string causes errors because MongoDB expects exact strings.

2. Step 2: Validate other options

   Collection name and query syntax are valid; 'executionStats' is supported.

3. Final Answer:

   The verbosity string 'executionStats' is misspelled -> Option A

4. Quick Check:

   Correct spelling of verbosity string is required [OK]

Hint: Check spelling of verbosity string in explain() [OK]

Common Mistakes:

• Misspelling 'executionStats' as 'executionStat'
• Assuming explain() doesn't support 'executionStats'
• Blaming query syntax without checking verbosity

5. You want to optimize a query that scans 1000 documents but returns only 2. The explain output shows totalDocsExamined: 1000 and nReturned: 2. What is the best next step?

hard

A. Rewrite the query to return all documents in the collection

B. Increase the batch size to return more documents faster

C. Add an index on the query fields to reduce scanned documents

D. Ignore the explain output; it does not affect performance

Solution

1. Step 1: Analyze the explain stats

   High totalDocsExamined but low nReturned means many documents scanned unnecessarily.

2. Step 2: Choose optimization strategy

   Adding an index on query fields helps MongoDB scan fewer documents and improve speed.

3. Final Answer:

   Add an index on the query fields to reduce scanned documents -> Option C

4. Quick Check:

   Index reduces scanned docs, improves query [OK]

Hint: High scanned vs returned? Add index on query fields [OK]

Common Mistakes:

• Ignoring explain output and performance impact
• Increasing batch size without fixing scan issue
• Trying to return all documents unnecessarily