Learning MongoDB - Query Optimization & explain() Analysis

#Introduction

In episode 12 you understood indexes in theory. But in the real world, "I think this query uses an index" is never enough — you need evidence. How do you know which queries are slow, why they're slow, and whether a new index actually works? The answer is in explain() and the database profiler.

Episode 13 teaches you to become a performance detective. The roadmap: first we read the execution plan with explain("executionStats"), second we dissect the difference between COLLSCAN and IXSCAN, third we understand winning plans versus rejected plans, fourth we measure efficiency with key metrics, and fifth we find slow queries in production using the profiler. Let's get started.

#Analyzing the Query Execution Plan

#explain("executionStats")

explain() shows how MongoDB executes a query — which plan was chosen and how much work was done. Add the "executionStats" option to see real execution statistics:

db.users.find({ email: "siti@example.com" }).explain("executionStats")

The result is a large document full of plan details. The most important part is in executionStats. Let's dissect the key fields you must know:

• nReturned — how many documents were returned.
• totalKeysExamined — how many index entries were examined.
• totalDocsExamined — how many documents were actually opened from the collection.
• executionTimeMillis — total execution time in milliseconds.

The golden rule of interpretation: totalDocsExamined should be close to nReturned. If MongoDB opens 100,000 documents just to return 10, it's scanning documents it doesn't need — a sign the index isn't right.

#COLLSCAN vs IXSCAN

Inside the plan, you'll find the main stage that tells the whole story:

• IXSCAN — index scan. MongoDB uses an index to find documents. This is what we want.
• COLLSCAN — collection scan. MongoDB reads every document one by one. This is the main enemy.

executionStats: {
  nReturned: 10,
  totalKeysExamined: 10,
  totalDocsExamined: 10,
  executionTimeMillis: 2,
  executionStages: {
    stage: "FETCH",
    inputStage: { stage: "IXSCAN", indexName: "email_1" }
  }
}

In the healthy example above: totalKeysExamined: 10 equals nReturned: 10, totalDocsExamined: 10, and the stage is IXSCAN on the email_1 index. This query is optimal — every examined document is indeed returned.

Conversely, if you see COLLSCAN and totalDocsExamined far exceeding nReturned, that query is the slow query suspect.

#Winning Plan vs Rejected Plans

MongoDB doesn't just use one index per query. For queries with several potential indexes, the optimizer runs multiple candidate plans in parallel, measures their cost, then chooses the best one:

• winningPlan — the plan that won and is used to execute the query.
• rejectedPlans — the plans that lost in the optimizer competition.

db.orders.find(
  { userId: ObjectId("66aaaaaaaaaaaaaaaaaaaaaaaa"), status: "paid" }
).explain("allPlansExecution")

Using the "allPlansExecution" option displays the statistics of all tested plans. This part is very useful when testing a new index: you can see whether the index you created became the winningPlan or is still beaten by another index. If your new index always appears in rejectedPlans, it doesn't provide an advantage — or the field order of its compound index isn't right (remember the ESR rule from episode 12).

#Metrics and Optimization Techniques

#Identifying Queries with Hard Filters at the Front

The most expensive queries are those that can't use an index at all. Their characteristics:

• $where and $regex operators without an anchor at the start of the pattern.
• Negations like $ne, $nin, $not on fields that should use an index.
• Operations on fields with mixed data types.

#Checking Index Coverage with explain

Test index effectiveness by adding it and then comparing executionStats before and after:

db.orders.createIndex({ userId: 1, status: 1 })
 
db.orders.find(
  { userId: ObjectId("66aaaaaaaaaaaaaaaaaaaaaaaa"), status: "paid" }
).explain("executionStats")

Run explain before and after creating the index. Before, you might see COLLSCAN; after, IXSCAN with drastically lower totalDocsExamined. This is how you prove an index's value with data, not feelings.

#Identifying Slow Queries

#Enabling the Database Profiler

Slow queries rarely come wearing a sign. To find them systematically, enable the database profiler, which records operations exceeding a certain time threshold:

db.setProfilingLevel(1, { slowms: 100 })

Level 1 means enabled and records operations exceeding slowms. Level 0 turns it off, and level 2 records all operations (only for momentary debugging, since it's heavy).

#Analyzing system.profile

All recorded operations are stored in the system.profile collection in the same database. You can query this collection like any other:

db.system.profile.find({ millis: { $gt: 100 } })
  .sort({ ts: -1 })
  .limit(10)

Each document contains the original query, execution time, and a planSummary showing whether it used an index. This is where you get undeniable evidence: which queries take time, how often, and which index they use. Then optimize with a new index or a query rewrite, and verify with explain.

#Conclusion

In episode 13 you became a performance detective: reading the execution plan with explain("executionStats"), distinguishing the bad COLLSCAN from the good IXSCAN, understanding winningPlan versus rejectedPlans in the optimizer competition, measuring efficiency through the totalKeysExamined and totalDocsExamined metrics, and enabling the database profiler to find slow queries in production via system.profile.

Key takeaways:

• totalDocsExamined should be close to nReturned; if not, there's an index issue.
• COLLSCAN = the enemy; IXSCAN = what we want.
• rejectedPlans shows the losing index — use it to validate index design.
• Profiler level 1 with slowms finds slow queries in production.
• Always verify fixes with explain before and after.

In the next episode, episode 14, we enter one of the most awaited features: Multi-Document ACID Transactions. You'll understand when transactions are needed, write transactions that change several documents across collections atomically — for example a balance transfer between accounts — and learn its limitations and best practices. See you in episode 14!