Execution plan (explain) and query performance analysis

Basic Concepts of Execution Plans (explain)

MongoDB's explain() method is a powerful diagnostic tool that reveals the execution details of a query. By appending .explain() to a query statement, you can obtain the execution plan chosen by the query optimizer. The execution plan contains critical information about how the query is processed, including index usage, the number of documents scanned, execution time, and more.

// Basic usage example
db.collection.find({ name: "张三" }).explain()

Execution plans have three levels of detail:

• queryPlanner (default): Shows only the plan selected by the query optimizer
• executionStats: Includes actual execution statistics
• allPlansExecution: Displays complete information for all candidate plans

Understanding Execution Plan Output

The core sections of the execution plan output include the following key information:

1. queryPlanner: Displays the query plan selected by the optimizer

   • winningPlan: The chosen execution plan
   • rejectedPlans: Rejected candidate plans

2. executionStats (when using this mode):

   • executionTimeMillis: Total execution time
   • totalKeysExamined: Number of index keys examined
   • totalDocsExamined: Number of documents examined
   • nReturned: Number of documents returned

// Example to get detailed execution statistics
db.orders.find({ status: "shipped" }).explain("executionStats")

Index Usage Analysis

Indexes are critical to query performance. The execution plan can confirm whether a query effectively uses indexes:

1. IXSCAN: Indicates an index scan, which is ideal
2. COLLSCAN: Indicates a full collection scan, which typically performs poorly

// Example of creating an index
db.products.createIndex({ category: 1, price: -1 })

// Checking index usage
db.products.find({ 
  category: "electronics", 
  price: { $gt: 500 } 
}).explain("executionStats")

When you see an IXSCAN stage, you can check the indexName to confirm which specific index was used. If you encounter COLLSCAN, consider creating an appropriate index.

Key Query Performance Metrics

When analyzing an execution plan, pay special attention to the following metrics:

1. Query Selectivity:

   • The ratio nReturned / totalDocsExamined should be as high as possible
   • Ideally, this ratio should be close to 1

2. Memory Usage:

   • The works field indicates the workload of the operation
   • advanced indicates the number of documents returned early

3. Stage Execution Order:

   • MongoDB execution plans are structured as a tree of stages
   • Child stages execute first, and their results are passed to parent stages

// Example of a compound query
db.users.find({
  age: { $gt: 30 },
  city: "北京",
  lastLogin: { $gt: new Date("2023-01-01") }
}).explain("executionStats")

Diagnosing Common Performance Issues

1. Full Collection Scan (COLLSCAN):

   • Symptom: totalDocsExamined is close to the total number of documents in the collection
   • Solution: Create an appropriate index for the query conditions

2. Inefficient Index Usage:

   • Symptom: totalKeysExamined is much larger than nReturned
   • Solution: Optimize the index or query conditions

3. In-Memory Sorting (SORT):

   • Symptom: A SORT stage appears and memLimit is exceeded
   • Solution: Create an index for the sort field

// Example of an in-memory sorting issue
db.employees.find({ department: "IT" })
  .sort({ salary: -1 })
  .explain("executionStats")

Advanced Execution Plan Analysis Techniques

1. Index Intersection:

   • MongoDB can use the intersection of multiple indexes
   • The execution plan will show AND_SORTED or AND_HASH stages

2. Covered Queries:

   • When a query requires only indexed fields, it can avoid reading documents entirely
   • Check if the indexOnly field is true

// Example of a covered query
db.customers.createIndex({ email: 1, name: 1 })
db.customers.find({ email: "user@example.com" }, { _id: 0, name: 1 })
  .explain("executionStats")

3. Query Shape Analysis:
   • Queries with the same "shape" will reuse query plans
   • Use $planCacheStats to view the plan cache

Practical Case Studies

Case 1: Product Search for an E-commerce Website

// Original query
db.products.find({
  category: "electronics",
  price: { $lte: 1000 },
  rating: { $gte: 4 },
  inStock: true
}).sort({ price: 1 }).explain("executionStats")

// Optimization: Create a compound index
db.products.createIndex({ 
  category: 1, 
  inStock: 1, 
  rating: 1, 
  price: 1 
})

Case 2: User Activity Query for a Social Media App

// Pagination query performance issue
db.posts.find({ userId: "user123" })
  .sort({ createdAt: -1 })
  .skip(100)
  .limit(10)
  .explain("executionStats")

// Optimization: Use range queries instead of skip
const lastDate = getLastDisplayedPostDate() // Get from the frontend
db.posts.find({ 
  userId: "user123",
  createdAt: { $lt: lastDate }
})
.sort({ createdAt: -1 })
.limit(10)

Execution Plans and Sharded Clusters

In a sharded environment, execution plan analysis becomes more complex:

1. mergeSort: Results from different shards need to be merged and sorted
2. shardFilter: Filters out data that doesn't match the shard key range
3. clusterTime: Time overhead for coordination operations

// Example of a sharded cluster query
db.shardedCollection.find({ 
  region: "APAC",
  value: { $gt: 10000 } 
}).explain("executionStats")

Pay special attention to the shards field, which shows execution statistics for each shard. Uneven shard loads may indicate the need to adjust the sharding strategy.

Query Optimization Best Practices

1. Indexing Strategy:

   • Follow the ESR rule (Equality-Sort-Range)
   • Regularly review and remove unused indexes

2. Query Refactoring:

   • Avoid using $where and $exists
   • Limit the number of fields returned

3. Monitoring Tools:

   • Use db.currentOp() to monitor running queries
   • Configure slow query logs

// Set slow query threshold (in milliseconds)
db.setProfilingLevel(1, { slowms: 100 })

// View profiling results
db.system.profile.find().sort({ ts: -1 }).limit(10)

Execution Plans and Aggregation Pipelines

Analyzing execution plans for aggregation pipelines is more complex, as each stage can alter the data flow:

db.orders.aggregate([
  { $match: { status: "completed", date: { $gt: new Date("2023-01-01") } } },
  { $group: { _id: "$productId", total: { $sum: "$amount" } } },
  { $sort: { total: -1 } },
  { $limit: 10 }
]).explain("executionStats")

Key points to observe:

1. Pipeline optimization flags (e.g., early $match)
2. Input/output document counts for each stage
3. Memory usage (especially for $group and $sort stages)