Docs4dev
Docs
mongodb / v3.6 / reference / reference-explain-results.html

Explain Results

On this page

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

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:

"queryPlanner" : {
   "plannerVersion" : <int>,
   "namespace" : <string>,
   "indexFilterSet" : <boolean>,
   "parsedQuery" : {
      ...
   },
   "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:

"queryPlanner" : {
   "mongosPlannerVersion" : <int>,
   "winningPlan" : {
      "stage" : <STAGE1>,
      "shards" : [
         {
            "shardName" : <string>,
            "connectionString" : <string>,
            "serverInfo" : {
               "host" : <string>,
               "port" : <int>,
               "version" : <string>,
               "gitVersion" : <string>
            },
            "plannerVersion" : <int>,
            "namespace" : <string>,
            ...
            "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. 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:

  • Unsharded Collections
  • Sharded Collections

For unsharded collections, explain returns the following executionStats information:

"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.

"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.