5.5 Query Optimization & Explain Plan Analysis

Query Optimization & Explain Plan Analysis

Exam Focus: The MongoDB Certified Associate Developer Exam requires reading and interpreting JSON Explain Plan outputs, understanding the three explain verbosity modes (queryPlanner, executionStats, allPlansExecution), identifying key execution stages (COLLSCAN, IXSCAN, FETCH, SORT, PROJECTION_COVERED), evaluating diagnostic ratios (totalKeysExamined vs totalDocsExamined vs nReturned), and using .hint() to override query planner choices.

---

The MongoDB Query Optimizer & Plan Cache Lifecycle

When MongoDB receives a read query, the Query Optimizer evaluates the query shape (the combination of query predicate fields, sort fields, and projection fields). If a cached winning plan already exists in the Plan Cache for that query shape, MongoDB executes that plan immediately.

The Multi-Plan Candidate Race

If no cached plan exists (or if the plan was evicted), the optimizer initiates an empirical trial:

1. Candidate Plan Generation: The optimizer identifies all candidate indexes that could potentially satisfy the query.
2. The Execution Race (Trial Period): MongoDB executes each candidate plan in parallel across a small trial batch (typically the first few dozen documents or work units).
3. Winning Plan Selection: The candidate plan that returns results the fastest with the lowest work cost is crowned the Winning Plan.
4. Plan Caching: The winning plan is saved in the plan cache for subsequent executions of that query shape.

Incoming Query Shape ===> Plan Cache Lookup
                              |
            +-----------------+-----------------+
            | (Cache Hit)                       | (Cache Miss / Evicted)
            v                                   v
Execute Cached Plan               Launch Candidate Plans in Parallel
                                                |
                                                v
                                  Select Winning Plan & Cache It

Plan Cache Eviction Triggers

The plan cache for a collection is automatically cleared when:

• An index on the collection is created or dropped.
• The collection is dropped or re-indexed.
• The mongod server instance is restarted.
• A cached plan fails or degrades in performance during execution.

---

Explain Plan Verbosity Modes

To diagnose query performance and inspect execution stages, append .explain(verbosity) to any find(), aggregate(), update(), or delete() operation.

MongoDB supports three Verbosity Modes:

// 1. queryPlanner (Default)
db.orders.find({ customer_id: 101 }).explain("queryPlanner");

// 2. executionStats
db.orders.find({ customer_id: 101 }).explain("executionStats");

// 3. allPlansExecution
db.orders.find({ customer_id: 101 }).explain("allPlansExecution");

---

Anatomy of Execution Stages

The executionStages tree in an explain plan reveals the exact step-by-step pipeline MongoDB traversed to satisfy the query. Stages operate hierarchically from leaf nodes (inputStage) up to the root stage.

Core Execution Stages Reference

Unindexed Query Stage Tree:     [ COLLSCAN ] ===> Client

Standard Indexed Stage Tree:    [ IXSCAN ] === (RecordId) ===> [ FETCH ] ===> Client

Covered Query Stage Tree:       [ IXSCAN ] === (Index Keys) ==> [ PROJECTION_COVERED ] ===> Client

In-Memory Sort Stage Tree:      [ IXSCAN ] ===> [ FETCH ] ===> [ SORT (RAM) ] ===> Client

---

Core Diagnostic Metrics & Selectivity Ratios

When evaluating the executionStats block of an explain plan, three primary metrics reveal the efficiency and health of the query:

{
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 50,
    "executionTimeMillis": 2,
    "totalKeysExamined": 50,
    "totalDocsExamined": 50,
    "executionStages": {
      "stage": "FETCH",
      "nReturned": 50,
      "inputStage": {
        "stage": "IXSCAN",
        "nReturned": 50,
        "indexName": "customer_id_1_status_1"
      }
    }
  }
}

Metric Definitions

1. nReturned: The exact number of documents that matched the query predicate and were returned to the client cursor.
2. totalKeysExamined: The total number of B-tree index keys scanned by the query engine during the IXSCAN stage.
3. totalDocsExamined: The total number of documents fetched from disk or the WiredTiger memory cache during the FETCH or COLLSCAN stage.
4. executionTimeMillis: Total wall-clock time in milliseconds taken to execute the query.

The Selectivity Health Formulas

$\text{1. Index Selectivity Ratio} = \frac{\text{totalKeysExamined}}{\text{nReturned}}$ $\text{2. Document Scan Ratio} = \frac{\text{totalDocsExamined}}{\text{nReturned}}$

Diagnostic Interpretation Matrix

---

Overriding Plan Selection: The .hint() Method

In rare production scenarios where the query optimizer chooses a suboptimal plan (for example, due to atypical data distribution or stale plan cache metrics), developers can force MongoDB to use a specific index using .hint():

// Force MongoDB to use the 'idx_customer_status' index
db.orders.find({
  customer_id: 101,
  status: "PENDING"
}).hint("idx_customer_status");

// Alternatively, specify index key pattern in hint
db.orders.find({
  customer_id: 101,
  status: "PENDING"
}).hint({ customer_id: 1, status: 1 });

// Force a full collection scan (COLLSCAN) for benchmarking
db.orders.find({ customer_id: 101 }).hint({ $natural: 1 });

[!CAUTION] Use .hint() judiciously in production. If the specified index is dropped or schema access patterns evolve, queries hardcoded with .hint() will fail or degrade significantly.