Explain plan analysis (queryPlanner, executionStats) in MongoDB - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the queryPlanner section in MongoDB's explain plan?

The queryPlanner shows how MongoDB plans to execute a query, including which indexes it will use and the stages of the query execution. It helps understand the strategy MongoDB chooses before running the query.

beginner

What information does the executionStats section provide in MongoDB's explain output?

The executionStats section shows actual runtime details like how many documents were examined, how many were returned, and how long the query took. It helps measure query performance.

intermediate

In MongoDB explain output, what does a high number of totalDocsExamined indicate?

A high totalDocsExamined means MongoDB looked at many documents to find the results. This can suggest the query is inefficient and might benefit from an index.

intermediate

What does the winningPlan represent in the queryPlanner section?

The winningPlan is the chosen method MongoDB will use to run the query, such as which index or collection scan it will perform.

advanced

Why is it useful to compare queryPlanner and executionStats in MongoDB explain?

Comparing queryPlanner and executionStats helps verify if the planned query strategy actually performs well in practice, and if indexes are used effectively.

What does the queryPlanner section in MongoDB explain show?

A The network latency during query execution

B The actual time taken to run the query

C The number of documents returned

D The planned steps and indexes used to execute the query

The queryPlanner shows the planned execution strategy, including indexes and stages.

Which section of MongoDB explain output tells you how many documents were scanned?

A executionStats

B serverStatus

C queryPlanner

D indexStats

The executionStats section reports actual runtime metrics like documents scanned.

If a query's executionStats shows a high totalDocsExamined, what might that mean?

A The query is scanning many documents, possibly inefficient

B The query is using an index efficiently

C The query returned no results

D The query is very fast

High totalDocsExamined means many documents were scanned, which can indicate inefficiency.

What is the winningPlan in MongoDB explain?

A The fastest query result

B The chosen query execution plan

C The number of documents returned

D The server configuration

The winningPlan is the execution plan MongoDB selects to run the query.

Why should you look at both queryPlanner and executionStats?

A To see the server uptime

B To find network errors

C To check if the planned query matches actual performance

D To view user permissions

Comparing both helps confirm if the query plan is efficient in real execution.

Describe what information you find in the queryPlanner and executionStats sections of a MongoDB explain plan.

Think about planning vs actual execution

Explain why analyzing both the queryPlanner and executionStats is important for optimizing MongoDB queries.

Consider planning and real results

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