Study guide
5.5 Query Optimization & Explain Plan Analysis
MongoDB Associate Developer Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Document Model & MongoDB Architecture
1.1 Document Model Fundamentals & BSON Data Types 1.2 Databases, Collections & Schema Flexibility 1.3 Distributed Architecture Fundamentals: Replica Sets & Sharded Clusters
2 Chapter 2: CRUD Operations: Create & Read
2.1 Document Insertion Operations 2.2 Query Selectors & Filter Operators 2.3 Array Queries & Element Evaluation 2.4 Projections, Cursor Methods & Collation
3 Chapter 3: CRUD Operations: Update & Delete
3.1 Field Update Operators 3.2 Array Update Operators & Positional Modifiers 3.3 Document Replacement & Deletion Operations 3.4 Atomic Find-and-Modify Operations
4 Chapter 4: Aggregation Framework Mastery
4.1 Pipeline Fundamentals & Core Stages ($match, $project, $addFields) 4.2 Grouping, Reshaping & Array Manipulation ($group, $unwind, $replaceWith) 4.3 Multi-Document Joins & Faceted Pipelines ($lookup, $facet, $bucket) 4.4 Materialization & Window Operations ($out, $merge, $setWindowFields) 4.5 Atlas Search: Search Index Definitions & $search Queries
5 Chapter 5: Indexing Strategies & Performance Optimization
5.1 Index Types & Storage Mechanics 5.2 Compound Indexes & the Equality-Sort-Range (ESR) Rule 5.3 Multikey Indexes & Array Indexing Constraints 5.4 Specialized Index Types: TTL, Partial, Unique & Text 5.5 Query Optimization & Explain Plan Analysis
6 Chapter 6: Data Modeling & Schema Design Patterns
6.1 Embedding vs. Referencing Tradeoffs 6.2 Core Schema Design Patterns 6.3 Schema Validation with JSON Schema
7 Chapter 7: Application Development & Driver Integration
7.1 Connection Strings & Connection Pool Management 7.2 Driver CRUD Paradigms & Object Mapping 7.3 Cursor Batching, Streaming & Memory Handling 7.4 Write Concerns, Read Concerns & Retryable Operations
8 Chapter 8: Developer Tooling & Administration Essentials
8.1 mongosh Scripting & Administrative Commands 8.2 Data Ingestion & Export Utilities 8.3 Backup, Restore & Performance Telemetry 8.4 Atlas Sample Dataset & Data Explorer
5.5 Query Optimization & Explain Plan Analysis
Key Takeaways Explain This Topic AI
Explain plan verbosity modes provide three diagnostic levels: 'queryPlanner' (winning plan structure), 'executionStats' (runtime metrics), and 'allPlansExecution' (all candidate stats).
Key execution stages include 'COLLSCAN' (full scan), 'IXSCAN' (index scan), 'FETCH' (retrieving documents via RecordId), and 'SORT' (in-memory sort).
Core telemetry metrics are 'nReturned' (matching docs), 'totalKeysExamined' (index keys scanned), and 'totalDocsExamined' (documents fetched from storage).
The ideal index selectivity ratio achieves totalDocsExamined / nReturned = 1 and totalKeysExamined / nReturned = 1 (with totalDocsExamined = 0 for Covered Queries).
The '.hint()' cursor method forces the query optimizer to utilize a specific index, bypassing plan cache selection.
Last updated: September 2026
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:
Candidate Plan Generation: The optimizer identifies all candidate indexes that could potentially satisfy the query.
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).
Winning Plan Selection: The candidate plan that returns results the fastest with the lowest work cost is crowned the Winning Plan .
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");
Verbosity Mode Executes Query? Telemetry Provided & Output Scope
queryPlanner (Default) ❌ No Analyzes candidate plans and returns the structure of the winningPlan (stages, index names, bounds) and rejectedPlans without executing the query against data files.
executionStats ✅ Yes Executes the winning plan to completion. Returns all queryPlanner metadata plus actual runtime statistics: executionTimeMillis , nReturned , totalKeysExamined , totalDocsExamined , and per-stage timings.
allPlansExecution ✅ Yes Executes the winning plan to completion AND captures execution statistics for all candidate plans that competed during the planning trial phase.
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
Execution Stage Operational Role Performance Evaluation
COLLSCAN Collection Scan: Linearly scans every document in the collection data file. 🔴 Critical Anti-Pattern: Indicates a missing index; scales as O ( N ) O(N) O ( N ) .
IXSCAN Index Scan: Traverses B-tree index keys within specified indexBounds . 🟢 Optimal: Logarithmic key scan using an index.
FETCH Document Fetch: Retrieves full BSON documents from disk/cache using RecordId . 🟡 Standard: Necessary when query/projection requires fields not in index.
PROJECTION_COVERED Covered Projection: Returns data directly from index keys without FETCH . 🟢 Peak Efficiency: totalDocsExamined: 0 ; zero collection I/O.
SORT In-Memory Sort: Loads matching documents into RAM to perform blocking sort. 🔴 Suboptimal: Indicates missing sort index; risk of 100 MB memory abort.
SORT_KEY_GENERATOR Computes sort keys for documents before feeding an in-memory SORT stage. 🟡 Precedes in-memory SORT .
AND_SORTED / AND_HASH Combines results from multiple IXSCAN stages (Index Intersection). 🟡 Merges two distinct single-field indexes.
LIMIT / SKIP Applies pagination boundaries on the incoming document stream. 🟢 Traversal control. 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
nReturned : The exact number of documents that matched the query predicate and were returned to the client cursor.
totalKeysExamined : The total number of B-tree index keys scanned by the query engine during the IXSCAN stage.
totalDocsExamined : The total number of documents fetched from disk or the WiredTiger memory cache during the FETCH or COLLSCAN stage.
executionTimeMillis : Total wall-clock time in milliseconds taken to execute the query.
The Selectivity Health Formulas 1. Index Selectivity Ratio = totalKeysExamined nReturned \text{1. Index Selectivity Ratio} = \frac{\text{totalKeysExamined}}{\text{nReturned}} 1. Index Selectivity Ratio = nReturned totalKeysExamined 2. Document Scan Ratio = totalDocsExamined nReturned \text{2. Document Scan Ratio} = \frac{\text{totalDocsExamined}}{\text{nReturned}} 2. Document Scan Ratio = nReturned totalDocsExamined
Diagnostic Interpretation Matrix
Metric Relationship Health Status Root Cause & Remediation
totalDocsExamined: 0 🟢 Covered Query Optimal. All filter, sort, and projected fields satisfied by index keys. Zero disk fetch I/O.
totalKeys = totalDocs = nReturned 🟢 Ideal Index Highly selective. Every index key scanned pointed directly to a matching document.
totalKeysExamined >> nReturned 🔴 Unselective Index Query scans thousands of index keys to find a few matching documents. Cause: Range filter placed before equality, or non-selective leading index field. Fix: Restructure under ESR rule.
totalDocsExamined >> nReturned 🔴 Post-Fetch Discard Index was used for initial filter, but remaining predicates required fetching documents and discarding them in memory. Fix: Add remaining filter fields to compound index.
totalDocsExamined > 0 & totalKeysExamined = 0 🔴 Full Scan ( COLLSCAN ) No index was utilized. Complete linear collection scan performed. Fix: Create index matching query filter.
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.