Explain plan analysis (queryPlanner, executionStats) in MongoDB - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Understanding queryPlanner output

Given the following MongoDB explain output snippet, what does the queryPlanner section primarily describe?

MongoDB

{
  "queryPlanner": {
    "plannerVersion": 1,
    "namespace": "shop.products",
    "indexFilterSet": false,
    "parsedQuery": { "category": { "$eq": "books" } },
    "winningPlan": {
      "stage": "IXSCAN",
      "keyPattern": { "category": 1 },
      "indexName": "category_1",
      "isMultiKey": false
    }
  },
  "executionStats": { /* omitted for brevity */ }
}

A It contains the error messages if the query failed.

B It shows the actual data returned by the query.

C It describes how MongoDB plans to execute the query, including indexes used.

D It lists all documents scanned during query execution.

💡 Hint

Think about what a 'planner' does before running a query.

The queryPlanner section explains the strategy MongoDB will use to run the query, such as which indexes it will use and the stages involved.

❓ query_result

intermediate

Interpreting executionStats nReturned value

In the executionStats section of a MongoDB explain output, what does the nReturned field represent?

MongoDB

{
  "executionStats": {
    "nReturned": 5,
    "executionTimeMillis": 3,
    "totalKeysExamined": 10,
    "totalDocsExamined": 5
  }
}

A The total number of documents in the collection.

B The number of index keys scanned but not returned.

C The number of queries run on the server.

D The number of documents matched and returned by the query.

💡 Hint

Focus on what 'returned' means in a query context.

nReturned tells how many documents the query actually returned to the user after execution.

🧠 Conceptual

advanced

Difference between queryPlanner and executionStats

Which statement best describes the difference between queryPlanner and executionStats in MongoDB's explain output?

A queryPlanner shows the database schema; executionStats shows user permissions.

B queryPlanner shows the planned steps before running; executionStats shows what actually happened during execution.

C queryPlanner shows server errors; executionStats shows network latency.

D queryPlanner shows the documents returned; executionStats shows the query syntax.

💡 Hint

Think about planning vs doing.

queryPlanner describes the intended plan before running the query, while executionStats reports the real performance and results after running.

📝 Syntax

advanced

Identify the error in explain command usage

Which of the following MongoDB commands will correctly return the explain plan with execution statistics for a find query on collection users filtering by age > 30?

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

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

C db.users.find.explain({ age: { $gt: 30 } }, 'executionStats')

D db.users.find({ age: { $gt: 30 } }).explain('allPlansExecution')

💡 Hint

Remember the order of method calls for explain.

The explain method is called on the cursor returned by find, with the verbosity string as argument.

❓ optimization

expert

Analyzing executionStats for query optimization

You run a query with explain('executionStats') and see totalKeysExamined: 10000 and totalDocsExamined: 10000. What does this suggest about the query's efficiency?

A The query is inefficient because it examines as many documents as index keys, likely scanning many documents.

B The query is efficient because it uses an index and examines few documents.

C The query is efficient because it scanned all documents quickly.

D The query failed because totalDocsExamined cannot equal totalKeysExamined.

💡 Hint

Think about what it means if keys examined equals documents examined.

If totalDocsExamined equals totalKeysExamined and both are large, it means the query scanned many documents, possibly due to poor index usage or no filtering.

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