MongoDB - Analyzing Queries

MongoDB - Analyzing Query Performance

Understanding and analyzing query performance is crucial for building scalable and efficient applications. A well-designed indexing strategy can dramatically improve database performance. In this chapter, we’ll explore modern tools and commands for query analysis, primarily focusing on the explain() method and the hint() modifier.

The Modern explain() Method

The explain() method provides detailed information on how MongoDB executes a query. It reveals whether indexes were used, which execution plan was chosen, and provides statistics on the operation. This is your primary tool for diagnosing slow queries.

Let’s assume we have a users collection and have created a compound index on the status and registrationDate fields:

// Using the modern mongosh shell
// Ensures the index exists for our test
db.users.createIndex({ status: 1, registrationDate: -1 });

Now, let’s analyze a query that finds all ‘active’ users registered this year. The key to modern analysis is using the 'executionStats' mode, which runs the query and provides detailed execution statistics.

db.users.find(
  {
    status: 'active',
    registrationDate: { $gte: new Date('2024-01-01T00:00:00Z') }
  }
).explain('executionStats');

The output of explain() in modern MongoDB versions is verbose but incredibly informative. Let’s break down the key sections:

/* A simplified example of modern explain() output */
{
  "queryPlanner": {
    "plannerVersion": 1,
    "namespace": "testdb.users",
    "winningPlan": {
      "stage": "FETCH",
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": { "status": 1, "registrationDate": -1 },
        "indexName": "status_1_registrationDate_-1",
        "direction": "forward",
        // ... other index details
      }
    }
  },
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 50,
    "executionTimeMillis": 5,
    "totalKeysExamined": 50,
    "totalDocsExamined": 50,
    "executionStages": {
      // ... detailed breakdown of each stage's execution
    }
  },
  "serverInfo": { /* ... */ },
  "ok": 1
}

How to Interpret the Explain Plan

• winningPlan.stage: This shows the final plan used. A top-level stage of FETCH with an inputStage of IXSCAN is ideal. It means MongoDB used an index (IXSCAN) to find the documents and then retrieved (FETCH) the full documents.
• COLLSCAN: If you see COLLSCAN (Collection Scan) as the winning plan’s stage, it’s a major red flag. It means MongoDB had to scan every single document in the collection, indicating no suitable index was found or used.
• executionStats.nReturned: The number of documents that match the query.
• executionStats.totalKeysExamined: The number of index keys scanned. In a perfectly efficient query, this number is very close to nReturned.
• executionStats.totalDocsExamined: The number of full documents scanned. Again, this should be close to nReturned.
• executionStats.executionTimeMillis: The total time taken by the query optimizer and execution engine.

Forcing an Index with hint()

The hint() method forces the query optimizer to use a specific index. This is useful for performance testing—for example, to see if one index performs better than another—but should be used with extreme caution in production. The query planner is usually better at choosing the optimal index.

// Force the use of the 'status_1_registrationDate_-1' index
db.users.find(
  { status: 'active' }
).hint({ status: 1, registrationDate: -1 });

You can combine hint() with explain() to see the performance characteristics of your chosen index.

Modern Best Practices & Tools

• Covered Queries: For maximum performance, create an index that includes all the fields your query needs to return. If a query can be satisfied entirely using the index (without fetching the document), it’s called a ‘covered query’. The explain() plan for a covered query will show an IXSCAN stage without a FETCH stage.
• MongoDB Compass: This official GUI tool for MongoDB provides a powerful visual ‘Explain Plan’ feature. It color-codes the plan and makes it much easier to understand performance bottlenecks without parsing raw JSON.
• MongoDB Atlas Performance Advisor: If you use MongoDB’s cloud service, Atlas, the Performance Advisor automatically analyzes slow queries and suggests new indexes to improve performance. It’s a proactive way to manage database efficiency.
• The Indexing Strategy: Follow the ESR (Equality, Sort, Range) rule when creating compound indexes. Fields for equality matches should come first, followed by sort fields, and finally range fields.