Explain
Plans

explain() reveals the execution plan MongoDB chose for a query — which index was used, how many documents and index keys were examined, and how long the query took. It is the primary tool for verifying index effectiveness and diagnosing slow queries.

// Attach .explain() to any find(), aggregate(), or cursor operation
db.users.find({ email: "user@example.com" }).explain()
db.users.find({ email: "user@example.com" }).explain("executionStats")
db.users.find({ email: "user@example.com" }).explain("allPlansExecution")

// For aggregation pipelines
db.orders.aggregate(
  [ { $match: { status: "active" } }, { $group: { _id: "$region", total: { $sum: "$amount" } } } ],
  { explain: true }
)

// Cursor method form (equivalent)
db.users.explain("executionStats").find({ email: "user@example.com" })

What explain() Returns

The output is a JSON document with three main sections:

• queryPlanner: the plan chosen by the optimizer, without running the query
• executionStats: actual runtime numbers — only present with "executionStats" or higher verbosity
• serverInfo: MongoDB version, host, and port

// queryPlanner — see the plan selection, no execution
db.orders.find({ status: "active" }).explain("queryPlanner")
// Returns: queryPlanner.winningPlan.stage, indexName — no timing data

// executionStats — run the query, get full metrics (use this for diagnosis)
db.orders.find({ status: "active" }).explain("executionStats")
// Returns: all of queryPlanner + executionStats section with:
//   nReturned, totalDocsExamined, totalKeysExamined, executionTimeMillis

// allPlansExecution — race all candidates, see why optimizer picked the winner
db.orders.find({ status: "active" }).explain("allPlansExecution")
// Returns: winning plan stats + allPlansExecution[].executionStages for each candidate
// Helps diagnose cases where optimizer makes a suboptimal choice

The explain output is verbose. These are the fields that directly diagnose query performance:

// Abridged executionStats output structure:
{
  queryPlanner: {
    winningPlan: {
      stage:     "IXSCAN",             // ← #1: is index used?
      indexName: "idx_status_created", // ← #2: which index?
      inputStage: {
        stage: "IXSCAN",
        // nested stages for complex plans (FETCH, SORT, etc.)
      }
    }
  },
  executionStats: {
    nReturned:          142,           // ← #3: documents sent to client
    totalKeysExamined:  142,           // ← #4: index entries scanned
    totalDocsExamined:  142,           // ← #5: disk document reads
    executionTimeMillis: 2,            // ← #6: total query time (ms)
    executionStages: {
      stage: "FETCH",
      inputStage: {
        stage:     "IXSCAN",
        keysExamined: 142
      }
    }
  }
}
// #7: Check for "SORT" stage in executionStages — means in-memory sort

Field Reference Table

The executionStages field is a nested tree — each stage has an optional inputStage. Execution flows from the innermost stage (bottom of the tree) upward. Understanding common stage combinations reveals how MongoDB processes your query.

Common Stage Names

Reading a Multi-Stage Tree

// Example: FETCH ← IXSCAN (typical indexed query with document fetch)
executionStages: {
  stage:        "FETCH",              // outer: fetch full doc from disk
  docsExamined: 142,
  inputStage: {
    stage:        "IXSCAN",           // inner: scan index first
    indexName:    "idx_email",
    keysExamined: 142
  }
}
// Data flow: IXSCAN finds 142 index entries → FETCH reads 142 documents from disk

// ❌ Bad example: SORT ← FETCH ← IXSCAN (in-memory sort after IXSCAN)
executionStages: {
  stage: "SORT",                      // outer: blocking in-memory sort
  memUsage: 4096000,
  inputStage: {
    stage: "FETCH",
    inputStage: {
      stage: "IXSCAN"                 // inner: index used but sort not eliminated
    }
  }
}
// Fix: add sort field to index in correct position relative to range fields (ESR)

Diagnosing Over-Scanning

// Example: totalKeysExamined: 50000, nReturned: 12
// Over-scan ratio: 50000 / 12 = 4166x — terrible

// Cause: index on { status: 1 } — status has only 3 values
// Each status group contains 50,000 documents — then filtered in-memory

// Fix 1: add high-selectivity field to narrow the scan
db.col.createIndex({ status: 1, customerId: 1 })

// Fix 2: use partial index if only one status value is queried frequently
db.col.createIndex({ customerId: 1 }, {
  partialFilterExpression: { status: "active" }
})

Identifying In-Memory Sort Overhead

// In explain output — look for SORT stage with memUsage
// This SORT stage means: all documents fetched, then sorted in memory

// Confirm it's a problem: check if memUsage is close to 100MB limit
executionStages.memUsage         // bytes used for in-memory sort
executionStages.memLimit         // the 100MB limit (104857600 bytes)

// If memUsage approaching memLimit, sort will fail on larger datasets
// Solution: add sort field to index before range fields (ESR rule)

.hint() forces MongoDB to use a specific index for a query, bypassing the query optimizer's plan selection. It is primarily a diagnostic and temporary mitigation tool — not a long-term solution.

// Force index by name
db.orders.find({ status: "active", createdAt: { $gte: ISODate("2024-01-01") } })
  .hint("idx_status_created")

// Force index by key specification
db.orders.find({ status: "active" })
  .hint({ status: 1, createdAt: -1 })

// Force COLLSCAN (useful for benchmarking or testing without index)
db.orders.find({ status: "active" })
  .hint({ $natural: 1 })
// $natural: 1 = forward collection scan; $natural: -1 = reverse scan

// hint() in aggregation pipeline
db.orders.aggregate(
  [ { $match: { status: "active" } } ],
  { hint: "idx_status_created" }
)

When to Use hint()

• Diagnosis: compare performance with explain("executionStats") using different indexes to understand which is actually faster
• Testing: verify that a newly created index is faster before removing the old one
• Optimizer bug: as a temporary hotfix when the optimizer consistently picks a suboptimal plan

Why the Optimizer Might Choose Wrong

// Scenario: optimizer picks a large general index over a targeted one
// Reason: optimizer's cost estimate is based on sampled data statistics,
// which may not reflect actual data distribution after bulk inserts/deletes

// Force re-evaluation of the query plan cache
db.orders.getPlanCache().clear()         // clear all cached plans for collection
db.orders.getPlanCache().clearPlansByQuery(
  { status: "active" }, // query shape
  {},                   // projection
  { status: 1 }         // sort
)

// Plan cache is also cleared automatically when:
// - An index is created or dropped
// - The collection is rebuilt
// - Data distribution changes significantly

$indexStats is an aggregation stage that returns per-index access statistics from a live, running MongoDB instance. It reveals how many times each index has been used since the last server restart, making it the authoritative source for spotting unused indexes in production.

// Get access statistics for all indexes on a collection
db.orders.aggregate([{ $indexStats: {} }])
// Output per index:
// {
//   name:   "idx_status_created",
//   key:    { status: 1, createdAt: -1 },
//   host:   "mongo-primary:27017",
//   accesses: {
//     ops:  8472,                   ← how many times this index was used
//     since: ISODate("2024-01-15")  ← when tracking started (usually restart)
//   }
// }

Finding Unused Index Candidates

// Find indexes with zero usage since tracking started
db.orders.aggregate([
  { $indexStats: {} },
  { $match: { "accesses.ops": 0 } },
  { $project: { name: 1, key: 1, "accesses.since": 1 } }
])
// These are candidates for the hidden index removal workflow

// Sort by usage count to see most/least used indexes
db.orders.aggregate([
  { $indexStats: {} },
  { $sort: { "accesses.ops": 1 } }    // ascending: least used first
])

$indexStats Limitations

Complete Index Health Checklist

// 1. List all indexes
db.collection.getIndexes()

// 2. Check index sizes
db.collection.stats().indexSizes

// 3. Check usage stats
db.collection.aggregate([{ $indexStats: {} }])

// 4. Verify critical queries use the right index
db.collection.find({ ... }).explain("executionStats")

// 5. Check WiredTiger cache pressure
db.serverStatus().wiredTiger.cache["bytes currently in the cache"]
db.serverStatus().wiredTiger.cache["maximum bytes configured"]
// If current ≈ maximum → cache pressure → indexes may be hitting disk