Skip to main content

explain()

The explain() method in MongoDB provides detailed information about how a query is executed, allowing you to analyze its performance characteristics. By using explain(), you can understand the query execution plan, which helps in optimizing queries for better performance.

Syntax​

The explain() method can be appended to a query like so:

db.collection.find({ field: value }).explain();

You can also specify the level of information you want by passing an argument to explain():

  • "queryPlanner": Provides the query execution plan without actually executing the query.
  • "executionStats": Provides the query execution plan along with statistics about the query execution.
  • "allPlansExecution": Provides information on all the execution plans that were considered by the query optimizer.

queryPlanner​

The queryPlanner section is one of the components in the output of MongoDB's explain() method. It provides detailed information about the query execution plan chosen by the query optimizer. This is crucial for understanding how a query will be executed, which indexes will be used, and how the documents will be scanned and filtered.

Key Components of queryPlanner​

  1. namespace: The namespace (database and collection) against which the query is run.

  2. indexFilterSet: Indicates whether index filters were set for the query planner. Usually false unless you've manually modified index filters.

  3. parsedQuery: Shows the query after it has been parsed into BSON format.

  4. winningPlan: Describes the optimal query plan chosen by the query planner. This includes details like the index used (IXSCAN), whether a collection scan (COLLSCAN) was done, and other stages like SORT, LIMIT, etc.

  5. rejectedPlans: Lists other query plans that were considered but not chosen. This can help you understand why a particular index was not used.

Example Usage​

To get the queryPlanner information for a query, you can use the explain() method like this:

db.collection.find({ field: value }).explain("queryPlanner")

Sample Output​

Here's a simplified example of what the queryPlanner section might look like:

{
  "queryPlanner": {
    "namespace": "mydb.mycollection",
    "indexFilterSet": false,
    "parsedQuery": {
      "field": {
        "$eq": "value"
      }
    },
    "winningPlan": {
      "stage": "IXSCAN",
      "indexName": "field_1",
      // ... additional details
    },
    "rejectedPlans": [
      // ... other plans that were considered
    ]
  }
}

Why is queryPlanner Important?​

  1. Performance Tuning: Understanding the queryPlanner output helps in identifying performance bottlenecks and allows you to choose the right indexes.

  2. Debugging: It's useful for debugging slow or problematic queries by showing you exactly how MongoDB is executing them.

  3. Cost Analysis: By looking at the winningPlan and rejectedPlans, you can get an idea of the "cost" associated with different query plans, helping you make more informed decisions about query and index optimization.

executionStats​

The executionStats section in the output of MongoDB's explain() method provides detailed statistics about the query's execution. Unlike the queryPlanner section, which describes the planned execution strategy, executionStats gives you actual metrics about how the query was executed. This is invaluable for performance tuning and debugging.

How to Use executionStats​

To get the executionStats information for a query, you can use the explain() method like this:

db.collection.find({ field: value }).explain("executionStats")

Key Components of executionStats​

  1. executionSuccess: Indicates whether the query was executed successfully.

  2. nReturned: The number of documents returned by the query.

  3. executionTimeMillis: The total time taken to execute the query, in milliseconds.

  4. totalKeysExamined: The total number of index keys examined.

  5. totalDocsExamined: The total number of documents examined, irrespective of whether they match the query criteria.

  6. executionStages: Provides a breakdown of the various stages involved in the query execution, similar to winningPlan in queryPlanner, but with actual runtime statistics.

  7. allPlansExecution: An array that contains statistics for all the query plans that were evaluated during the query optimization phase.

Sample Output​

Here's a simplified example of what the executionStats section might look like:

{
  "executionStats": {
    "executionSuccess": true,
    "nReturned": 1,
    "executionTimeMillis": 0,
    "totalKeysExamined": 1,
    "totalDocsExamined": 1,
    "executionStages": {
      "stage": "IXSCAN",
      "nReturned": 1,
      "executionTimeMillisEstimate": 0,
      // ... additional details
    },
    "allPlansExecution": [
      // ... statistics for other plans evaluated
    ]
  }
}

Why is executionStats Important?​

  1. Performance Analysis: executionStats helps you understand the actual cost of a query in terms of time and resources, allowing you to fine-tune both your queries and your indexes.

  2. Debugging: It's invaluable for debugging performance issues, helping you understand why a query might be slow or resource-intensive.

  3. Optimization: By looking at metrics like totalKeysExamined and totalDocsExamined, you can get insights into how well your indexes are performing and whether you need additional or different indexes.

allPlansExecution​

The allPlansExecution field is part of the executionStats section in the output of MongoDB's explain() method. This field provides an array of statistics for all query plans that were evaluated during the query optimization phase. It allows you to see how each plan performed, even if it wasn't ultimately chosen as the winningPlan.

Key Components of allPlansExecution​

Each object in the allPlansExecution array typically contains the following fields:

  1. nReturned: The number of documents that would be returned by this plan.

  2. executionTimeMillisEstimate: The estimated time in milliseconds that this plan would take to execute.

  3. totalKeysExamined: The total number of index keys examined during the execution of this plan.

  4. totalDocsExamined: The total number of documents examined during the execution of this plan.

  5. works: An internal metric representing the number of work units performed by this plan.

  6. advanced: The number of documents that passed all the filters and transformations in the plan.

  7. needTime, needFetch, saveState, restoreState, isEOF: These are additional internal metrics and flags used by MongoDB to evaluate the plan.

Example Usage​

To see allPlansExecution details, you can run:

db.collection.find({ field: value }).explain("executionStats")

Sample Output​

Here's a simplified example of what the allPlansExecution section might look like:

{
  "executionStats": {
    // ... other fields
    "allPlansExecution": [
      {
        "nReturned": 1,
        "executionTimeMillisEstimate": 0,
        "totalKeysExamined": 1,
        "totalDocsExamined": 1,
        // ... additional details
      },
      // ... other plans
    ]
  }
}

Why is allPlansExecution Important?​

  1. Plan Comparison: It allows you to compare the efficiency of different query plans, helping you understand why the query planner chose one plan over the others.

  2. Performance Tuning: By examining how each plan performs, you can make more informed decisions about index creation and query optimization.

  3. Debugging: It can be useful for debugging query performance issues, giving you insights into how MongoDB evaluates different plans.

Information Provided by explain()​

Here are some key fields that you'll find in the output of explain():

  • queryPlanner: Contains details about the query planning phase.

    • winningPlan: The execution plan that was selected.
    • rejectedPlans: Other plans that were considered but not chosen.

  • executionStats: Contains statistics about the query execution.

    • executionTimeMillis: Time taken to execute the query in milliseconds.
    • totalDocsExamined: Total number of documents scanned.
    • totalKeysExamined: Total number of index keys scanned.

  • serverInfo: Information about the MongoDB server.

Example​

Let's consider a simple example where we have a collection named users and we want to find documents where the age field is 30.

db.users.find({ age: 30 }).explain("executionStats");

This will return a detailed explanation including:

  • The index that was used (if any).
  • The number of documents examined.
  • The time taken to execute the query.

How is the winningPlan Chosen in MongoDB?​

The process of selecting a winningPlan for a query in MongoDB is a crucial aspect of query optimization. MongoDB's query planner evaluates multiple query plans and chooses the one that is most efficient based on various metrics. Here's how the process generally works:

1. Candidate Plan Generation​

First, the query planner identifies all the possible plans for executing a query. These could range from collection scans (COLLSCAN) to various index scans (IXSCAN) depending on the available indexes.

2. Plan Evaluation​

MongoDB then runs these candidate plans in parallel for a brief period, collecting statistics like:

  • Number of documents returned (nReturned)
  • Number of keys examined (totalKeysExamined)
  • Number of documents examined (totalDocsExamined)
  • Estimated execution time (executionTimeMillisEstimate)

3. Scoring and Ranking​

Based on the collected statistics, each plan is scored. The scoring algorithm considers factors like:

  • How quickly the plan returns results
  • How many resources (CPU, I/O) are used
  • How many documents and keys are examined

4. Selecting the winningPlan​

The plan with the best score is selected as the winningPlan. This is the plan that MongoDB estimates will be the most efficient for retrieving the required data.

5. Plan Caching​

The winningPlan is often cached so that it can be reused for similar queries in the future, thereby reducing the overhead of plan evaluation.

6. Re-evaluation​

The winningPlan is not set in stone. If the underlying data changes significantly, or if new indexes are added or removed, MongoDB may invalidate the cached plan and trigger a new round of plan evaluation.

Factors Affecting winningPlan Selection​

  • Available Indexes: The types and configurations of indexes on the collection.

  • Query Complexity: More complex queries with multiple conditions, joins, or aggregations may require more sophisticated plans.

  • Data Distribution: The distribution of data can affect which plan is most efficient. For example, an index scan might be efficient for a skewed data distribution but not for uniformly distributed data.

  • System Resources: Available CPU, memory, and disk I/O can also influence plan selection.

Use Cases​

  • Identifying Index Usage: Helps you understand if your query is using indexes effectively.
  • Performance Tuning: Allows you to identify bottlenecks and areas for optimization.
  • Debugging: Helps in understanding why a particular query is not returning the expected results.

Considerations​

  • Running explain() with "executionStats" or "allPlansExecution" will actually execute the query, which might be resource-intensive for large datasets.

  • The explain() output can be quite verbose, especially for complex queries. It's crucial to focus on the most relevant information for your use case.