Explain Results¶

On this page

Explain Output
3.0 Format Change

To return information on query plans and execution statistics of the query plans, MongoDB provides:

the db.collection.explain() method,
the cursor.explain() method, and
the explain command.

The explain results present the query plans as a tree of stages.

"winningPlan" : {
   "stage" : <STAGE1>,
   ...
   "inputStage" : {
      "stage" : <STAGE2>,
      ...
      "inputStage" : {
         "stage" : <STAGE3>,
         ...
      }
   }
},

Each stage passes its results (i.e. documents or index keys) to the parent node. The leaf nodes access the collection or the indices. The internal nodes manipulate the documents or the index keys that result from the child nodes. The root node is the final stage from which MongoDB derives the result set.

Stages are descriptive of the operation; e.g.

COLLSCAN for a collection scan
IXSCAN for scanning index keys
FETCH for retrieving documents
SHARD_MERGE for merging results from shards
SHARDING_FILTER for filtering out orphan documents from shards

Explain Output¶

The following sections presents a list of some key fields returned by the explain operation.

Note

The list of fields is not meant to be exhaustive, but is meant to highlight some key field changes from earlier versions of explain.
The output format is subject to change between releases.

`queryPlanner`¶

queryPlanner information details the plan selected by the query optimizer.

Unsharded Collections
Sharded Collections

For unsharded collections, explain returns the following queryPlanner information:

copy

"queryPlanner" : {
   "plannerVersion" : <int>,
   "namespace" : <string>,
   "indexFilterSet" : <boolean>,
   "parsedQuery" : {
      ...
   },
   "queryHash" : <hexadecimal string>,
   "planCacheKey" : <hexadecimal string>,
   "optimizedPipeline" : <boolean>, // Starting in MongoDB 4.2, only appears if true
   "winningPlan" : {
      "stage" : <STAGE1>,
      ...
      "inputStage" : {
         "stage" : <STAGE2>,
         ...
         "inputStage" : {
            ...
         }
      }
   },
   "rejectedPlans" : [
      <candidate plan 1>,
      ...
   ]
}

For sharded collections, explain includes the core query planner and server information for each accessed shard in the shards field:

copy

"queryPlanner" : {
   "mongosPlannerVersion" : <int>,
   "winningPlan" : {
      "stage" : <STAGE1>,
      "shards" : [
         {
            "shardName" : <string>,
            "connectionString" : <string>,
            "serverInfo" : {
               "host" : <string>,
               "port" : <int>,
               "version" : <string>,
               "gitVersion" : <string>
            },
            "plannerVersion" : <int>,
            "namespace" : <string>,
            "parsedQuery" : <document>,
            "queryHash" : <hexadecimal string>,
            "planCacheKey" : <hexadecimal string>,
            "optimizedPipeline" : <boolean>, // Starting in MongoDB 4.2, only appears if true
            "winningPlan" : {
               "stage" : <STAGE2>,
               "inputStage" : {
                  "stage" : <STAGE3>
                  ...,
               }
            },
            "rejectedPlans" : [
               <candidate plan 1>,
               ...
            ]
         },
         ...
      ]
   }
}

explain.queryPlanner¶

Contains information on the selection of the query plan by the query optimizer.

explain.queryPlanner.namespace¶: A string that specifies the namespace (i.e., <database>.<collection>) against which the query is run.

explain.queryPlanner.indexFilterSet¶: A boolean that specifies whether MongoDB applied an index filter for the query shape.

explain.queryPlanner.queryHash¶

A hexadecimal string that represents the hash of the query shape and is dependent only on the query shapes. queryHash can help identify slow queries (including the query filter of write operations) with the same query shape.

Note

As with any hash function, two different query shapes may result in the same hash value. However, the occurrence of hash collisions between different query shapes is unlikely.

For more information on queryHash and planCacheKey, see queryHash and planCacheKey.

New in version 4.2.

explain.queryPlanner.planCacheKey¶

A hash of the key for the plan cache entry associated with the query.

Unlike the queryHash, the planCacheKey is a function of both the query shape and the currently available indexes for that shape. That is, if indexes that can support the query shape are added/dropped, the planCacheKey value may change whereas the queryHash value would not change.

For more information on queryHash and planCacheKey, see queryHash and planCacheKey.

New in version 4.2.

explain.queryPlanner.optimizedPipeline¶

A boolean that indicates that the entire aggregation pipeline operation was optimized away, and instead, fulfilled by a tree of query plan execution stages.

For example, starting in MongodB 4.2, the following aggregation operation can be fulfilled by the tree of query plan execution rather than using the aggregation pipeline.

copy

db.example.aggregate([ { $match: { someFlag: true } } ] )

The field is only present if the value is true and only applies to explain on aggregation pipeline operations. When true, because the pipeline was optimized away, no aggregation stage information appears in the output.

New in version 4.2.

explain.queryPlanner.winningPlan¶

A document that details the plan selected by the query optimizer. MongoDB presents the plan as a tree of stages; i.e. a stage can have an inputStage or, if the stage has multiple child stages, inputStages.

explain.queryPlanner.winningPlan.stage¶

A string that denotes the name of the stage.

Each stage consists of information specific to the stage. For instance, an IXSCAN stage will include the index bounds along with other data specific to the index scan. If a stage has a child stage or multiple child stages, the stage will have an inputStage or inputStages.

explain.queryPlanner.winningPlan.inputStage¶: A document that describes the child stage, which provides the documents or index keys to its parent. The field is present if the parent stage has only one child.

explain.queryPlanner.winningPlan.inputStages¶: An array of documents describing the child stages. Child stages provide the documents or index keys to the parent stage. The field is present if the parent stage has multiple child nodes. For example, stages for $or expressions or index intersection consume input from multiple sources.

explain.queryPlanner.rejectedPlans¶: Array of candidate plans considered and rejected by the query optimizer. The array can be empty if there were no other candidate plans.

`executionStats`¶

The returned executionStats information details the execution of the winning plan. In order to include executionStats in the results, you must run the explain in either:

executionStats or
allPlansExecution verbosity mode. Use allPlansExecution mode to include partial execution data captured during plan selection.

Unsharded Collections
Sharded Collections

For unsharded collections, explain returns the following executionStats information:

copy

"executionStats" : {
   "executionSuccess" : <boolean>,
   "nReturned" : <int>,
   "executionTimeMillis" : <int>,
   "totalKeysExamined" : <int>,
   "totalDocsExamined" : <int>,
   "executionStages" : {
      "stage" : <STAGE1>
      "nReturned" : <int>,
      "executionTimeMillisEstimate" : <int>,
      "works" : <int>,
      "advanced" : <int>,
      "needTime" : <int>,
      "needYield" : <int>,
      "saveState" : <int>,
      "restoreState" : <int>,
      "isEOF" : <boolean>,
      ...
      "inputStage" : {
         "stage" : <STAGE2>,
         "nReturned" : <int>,
         "executionTimeMillisEstimate" : <int>,
         ...
         "inputStage" : {
            ...
         }
      }
   },
   "allPlansExecution" : [
      {
         "nReturned" : <int>,
         "executionTimeMillisEstimate" : <int>,
         "totalKeysExamined" : <int>,
         "totalDocsExamined" :<int>,
         "executionStages" : {
            "stage" : <STAGEA>,
            "nReturned" : <int>,
            "executionTimeMillisEstimate" : <int>,
            ...
            "inputStage" : {
               "stage" : <STAGEB>,
               ...
               "inputStage" : {
                 ...
               }
            }
         }
      },
      ...
   ]
}

For sharded collections, explain includes the execution statistics for each accessed shard.

copy

"executionStats" : {
   "nReturned" : <int>,
   "executionTimeMillis" : <int>,
   "totalKeysExamined" : <int>,
   "totalDocsExamined" : <int>,
   "executionStages" : {
      "stage" : <STAGE1>
      "nReturned" : <int>,
      "executionTimeMillis" : <int>,
      "totalKeysExamined" : <int>,
      "totalDocsExamined" : <int>,
      "totalChildMillis" : <NumberLong>,
      "shards" : [
         {
            "shardName" : <string>,
            "executionSuccess" : <boolean>,
            "executionStages" : {
               "stage" : <STAGE2>,
               "nReturned" : <int>,
               "executionTimeMillisEstimate" : <int>,
               ...
               "chunkSkips" : <int>,
               "inputStage" : {
                  "stage" : <STAGE3>,
                  ...
                  "inputStage" : {
                     ...
                  }
               }
            }
         },
         ...
      ]
   }
   "allPlansExecution" : [
      {
         "shardName" : <string>,
         "allPlans" : [
            {
               "nReturned" : <int>,
               "executionTimeMillisEstimate" : <int>,
               "totalKeysExamined" : <int>,
               "totalDocsExamined" :<int>,
               "executionStages" : {
                  "stage" : <STAGEA>,
                  "nReturned" : <int>,
                  "executionTimeMillisEstimate" : <int>,
                  ...
                  "inputStage" : {
                     "stage" : <STAGEB>,
                     ...
                     "inputStage" : {
                       ...
                     }
                  }
               }
            },
            ...
         ]
      },
      {
         "shardName" : <string>,
         "allPlans" : [
          ...
         ]
      },
      ...
   ]
}

explain.executionStats¶

Contains statistics that describe the completed query execution for the winning plan. For write operations, completed query execution refers to the modifications that would be performed, but does not apply the modifications to the database.

explain.executionStats.nReturned¶: Number of documents that match the query condition. nReturned corresponds to the n field returned by cursor.explain() in earlier versions of MongoDB.

explain.executionStats.executionTimeMillis¶: Total time in milliseconds required for query plan selection and query execution. executionTimeMillis corresponds to the millis field returned by cursor.explain() in earlier versions of MongoDB.

explain.executionStats.totalKeysExamined¶: Number of index entries scanned. totalKeysExamined corresponds to the nscanned field returned by cursor.explain() in earlier versions of MongoDB.

explain.executionStats.totalDocsExamined¶: Number of documents examined during query execution. Common query execution stages that examine documents are COLLSCAN and FETCH.

Note

totalDocsExamined refers to the total number of documents examined and not to the number of documents returned. For example, a stage can examine a document in order to apply a filter. If the document is filtered out, then it has been examined but will not be returned as part of the query result set.

If a document is examined multiple times during query execution, totalDocsExamined counts each examination. That is, totalDocsExamined is not a count of the total number of unique documents examined.

explain.executionStats.executionStages¶

Details the completed execution of the winning plan as a tree of stages; i.e. a stage can have an inputStage or multiple inputStages.

Each stage consists of execution information specific to the stage.

explain.executionStats.executionStages.executionTimeMillisEstimate¶: The estimated amount of time in milliseconds for query execution.

explain.executionStats.executionStages.works¶: Specifies the number of “work units” performed by the query execution stage. Query execution divides its work into small units. A “work unit” might consist of examining a single index key, fetching a single document from the collection, applying a projection to a single document, or doing a piece of internal bookkeeping.

explain.executionStats.executionStages.advanced¶: The number of intermediate results returned, or advanced, by this stage to its parent stage.

explain.executionStats.executionStages.needTime¶: The number of work cycles that did not advance an intermediate result to its parent stage (see explain.executionStats.executionStages.advanced). For instance, an index scan stage may spend a work cycle seeking to a new position in the index as opposed to returning an index key; this work cycle would count towards explain.executionStats.executionStages.needTime rather than explain.executionStats.executionStages.advanced.

explain.executionStats.executionStages.needYield¶: The number of times that the storage layer requested that the query stage suspend processing and yield its locks.

explain.executionStats.executionStages.saveState¶: The number of times that the query stage suspended processing and saved its current execution state, for example in preparation for yielding its locks.

explain.executionStats.executionStages.restoreState¶: The number of times that the query stage restored a saved execution state, for example after recovering locks that it had previously yielded.

explain.executionStats.executionStages.isEOF¶

Specifies whether the execution stage has reached end of stream:

If true or 1, the execution stage has reached end-of-stream.
If false or 0, the stage may still have results to return. For example, consider a query with a limit whose execution stages consists of a LIMIT stage with an input stage of IXSCAN for the query. If the query returns more than the specified limit, the LIMIT stage will report isEOF: 1, but its underlying IXSCAN stage will report isEOF: 0.

explain.executionStats.executionStages.inputStage.keysExamined¶

For query execution stages that scan an index (e.g. IXSCAN), keysExamined is the total number of in-bounds and out-of-bounds keys that are examined in the process of the index scan. If the index scan consists of a single contiguous range of keys, only in-bounds keys need to be examined. If the index bounds consists of several key ranges, the index scan execution process may examine out-of-bounds keys in order to skip from the end of one range to the beginning of the next.

Consider the following example, where there is an index of field x and the collection contains 100 documents with x values 1 through 100:

copy

db.keys.find( { x : { $in : [ 3, 4, 50, 74, 75, 90 ] } } ).explain( "executionStats" )

The query will scan keys 3 and 4. It will then scan the key 5, detect that it is out-of-bounds, and skip to the next key 50.

Continuing this process, the query scans keys 3, 4, 5, 50, 51, 74, 75, 76, 90, and 91. Keys 5, 51, 76, and 91 are out-of-bounds keys that are still examined. The value of keysExamined is 10.

explain.executionStats.executionStages.inputStage.docsExamined¶

Specifies the number of documents scanned during the query execution stage.

Present for the COLLSCAN stage, as well as for stages that retrieve documents from the collection (e.g. FETCH)

explain.executionStats.executionStages.inputStage.seeks¶: New in version 3.4: For index scan (IXSCAN) stages only.

The number of times that we had to seek the index cursor to a new position in order to complete the index scan.

explain.executionStats.allPlansExecution¶: Contains partial execution information captured during the plan selection phase for both the winning and rejected plans. The field is present only if explain runs in allPlansExecution verbosity mode.

`serverInfo`¶

Unsharded Collections
Sharded Collections

For unsharded collections, explain returns the following serverInfo information for the MongoDB instance:

copy

"serverInfo" : {
   "host" : <string>,
   "port" : <int>,
   "version" : <string>,
   "gitVersion" : <string>
}

For sharded collections, explain returns the serverInfo for each accessed shard, and a top-level serverInfo object for the mongos.

copy

"queryPlanner" : {
   ...
   "winningPlan" : {
      "stage" : <STAGE1>,
      "shards" : [
         {
            "shardName" : <string>,
            "connectionString" : <string>,
            "serverInfo" : {
               "host" : <string>,
               "port" : <int>,
               "version" : <string>,
               "gitVersion" : <string>
            },
            ...
         }
         ...
      ]
   }
 },
 "serverInfo" : {      // serverInfo for mongos
   "host" : <string>,
   "port" : <int>,
   "version" : <string>,
   "gitVersion" : <string>
 }
 ...

3.0 Format Change¶

Starting in MongoDB 3.0, the format and fields of the explain results have changed from previous versions. The following lists some key differences.

Collection Scan vs. Index Use¶

If the query planner selects a collection scan, the explain result includes a COLLSCAN stage.

If the query planner selects an index, the explain result includes a IXSCAN stage. The stage includes information such as the index key pattern, direction of traversal, and index bounds.

In previous versions of MongoDB, cursor.explain() returned the cursor field with the value of:

BasicCursor for collection scans, and
BtreeCursor <index name> [<direction>] for index scans.

For more information on execution statistics of collection scans versus index scans, see Analyze Query Performance.

Covered Queries¶

When an index covers a query, MongoDB can both match the query conditions and return the results using only the index keys; i.e. MongoDB does not need to examine documents from the collection to return the results.

When an index covers a query, the explain result has an IXSCAN stage that is not a descendant of a FETCH stage, and in the executionStats, the totalDocsExamined is 0.

In earlier versions of MongoDB, cursor.explain() returned the indexOnly field to indicate whether the index covered a query.

Index Intersection¶

For an index intersection plan, the result will include either an AND_SORTED stage or an AND_HASH stage with an inputStages array that details the indexes; e.g.:

copy

{
   "stage" : "AND_SORTED",
   "inputStages" : [
      {
         "stage" : "IXSCAN",
         ...
      },
      {
         "stage" : "IXSCAN",
         ...
      }
   ]
}

In previous versions of MongoDB, cursor.explain() returned the cursor field with the value of Complex Plan for index intersections.

`$or` Expression¶

If MongoDB uses indexes for an $or expression, the result will include the OR stage with an inputStages array that details the indexes; e.g.:

copy

{
   "stage" : "OR",
   "inputStages" : [
      {
         "stage" : "IXSCAN",
         ...
      },
      {
         "stage" : "IXSCAN",
         ...
      },
      ...
   ]
}

In previous versions of MongoDB, cursor.explain() returned the clauses array that detailed the indexes.

Sort Stage¶

If MongoDB cannot use an index or indexes to obtain the sort order, the results include a SORT stage indicating a blocking sort operation. Blocking sorts do not block concurrent operations on the collection or database. The name refers to the requirement that the SORT stage reads all input documents before returning any output documents, blocking the flow of data for that specific query.

If MongoDB requires using more than 100 megabytes of system memory for the blocking sort operation, MongoDB returns an error unless the query specifies cursor.allowDiskUse() (New in MongoDB 4.4). allowDiskUse() allows MongoDB to use temporary files on disk to store data exceeding the 100 megabyte system memory limit while processing a blocking sort operation. If the explain plan does not contain an explicit SORT stage, then MongoDB can use an index to obtain the sort order.

Prior to MongoDB 3.0, cursor.explain() returned the scanAndOrder field to specify whether MongoDB could use the index order to return sorted results.

← Write Operation Performance Analyze Query Performance →

Explain Results¶

Explain Output¶

queryPlanner¶

executionStats¶

serverInfo¶

3.0 Format Change¶

Collection Scan vs. Index Use¶

Covered Queries¶

Index Intersection¶

$or Expression¶

Sort Stage¶

`queryPlanner`¶

`executionStats`¶

`serverInfo`¶

`$or` Expression¶