Explain Results

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

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

  1. "winningPlan" : {
  2.    "stage" : <STAGE1>,
  3.    ...
  4.    "inputStage" : {
  5.       "stage" : <STAGE2>,
  6.       ...
  7.       "inputStage" : {
  8.          "stage" : <STAGE3>,
  9.          ...
  10.       }
  11.    }
  12. },

Each stage passes its results (i.e. documents or index keys) to theparent node. The leaf nodes access the collection or the indices. Theinternal nodes manipulate the documents or the index keys that resultfrom the child nodes. The root node is the final stage from whichMongoDB 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 bythe explain operation.

Note

  • The list of fields is not meant to be exhaustive, but is meant tohighlight 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 bythe query optimizer.

  • Unsharded Collections
  • Sharded Collections

For unsharded collections, explain returns the followingqueryPlanner information:

  1. "queryPlanner" : {
  2.    "plannerVersion" : <int>,
  3.    "namespace" : <string>,
  4.    "indexFilterSet" : <boolean>,
  5.    "parsedQuery" : {
  6.       ...
  7.    },
  8.    "queryHash" : <hexadecimal string>,
  9.    "planCacheKey" : <hexadecimal string>,
  10.    "optimizedPipeline" : <boolean>, // Starting in MongoDB 4.2, only appears if true
  11.    "winningPlan" : {
  12.       "stage" : <STAGE1>,
  13.       ...
  14.       "inputStage" : {
  15.          "stage" : <STAGE2>,
  16.          ...
  17.          "inputStage" : {
  18.             ...
  19.          }
  20.       }
  21.    },
  22.    "rejectedPlans" : [
  23.       <candidate plan 1>,
  24.       ...
  25.    ]
  26. }

For sharded collections, explain includes the corequery planner and server information for each accessedshard in the shards field:

  1. "queryPlanner" : {
  2.    "mongosPlannerVersion" : <int>,
  3.    "winningPlan" : {
  4.       "stage" : <STAGE1>,
  5.       "shards" : [
  6.          {
  7.             "shardName" : <string>,
  8.             "connectionString" : <string>,
  9.             "serverInfo" : {
  10.                "host" : <string>,
  11.                "port" : <int>,
  12.                "version" : <string>,
  13.                "gitVersion" : <string>
  14.             },
  15.             "plannerVersion" : <int>,
  16.             "namespace" : <string>,
  17.             "parsedQuery" : <document>,
  18.             "queryHash" : <hexadecimal string>,
  19.             "planCacheKey" : <hexadecimal string>,
  20.             "optimizedPipeline" : <boolean>, // Starting in MongoDB 4.2, only appears if true
  21.             "winningPlan" : {
  22.                "stage" : <STAGE2>,
  23.                "inputStage" : {
  24.                   "stage" : <STAGE3>
  25.                   ...,
  26.                }
  27.             },
  28.             "rejectedPlans" : [
  29.                <candidate plan 1>,
  30.                ...
  31.             ]
  32.          },
  33.          ...
  34.       ]
  35.    }
  36. }
  • explain.queryPlanner

  • Contains information on the selection of the query plan by thequery 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 indexfilter for the query shape.

    • explain.queryPlanner.queryHash

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

Note

As with any hash function, two different query shapes may resultin the same hash value. However, the occurrence of hashcollisions 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 thequery.

Unlike the queryHash, theplanCacheKey is a function ofboth the query shape and the currently available indexes forthat shape. That is, if indexes that can support the queryshape are added/dropped, the planCacheKey value may changewhereas 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 pipelineoperation was optimized away, and instead, fulfilled by a treeof query plan execution stages.

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

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

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

New in version 4.2.

  • explain.queryPlanner.winningPlan

  • A document that details the plan selected by the queryoptimizer. MongoDB presents the plan as atree of stages; i.e. a stage can have aninputStage or, if thestage 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. Forinstance, an IXSCAN stage will include the index boundsalong with other data specific to the index scan. If a stagehas a child stage or multiple child stages, the stage will have aninputStage or inputStages.

  1. - <code>explain.queryPlanner.winningPlan.</code><code>inputStage</code>[]($b09beaeefc00b1c7.md#explain.queryPlanner.winningPlan.inputStage)
  2. -

A document that describes the child stage, which provides thedocuments or index keys to its parent. The field is presentif the parent stage has only one child.

  1. - <code>explain.queryPlanner.winningPlan.</code><code>inputStages</code>[]($b09beaeefc00b1c7.md#explain.queryPlanner.winningPlan.inputStages)
  2. -

An array of documents describing the child stages. Childstages provide the documents or index keys to the parentstage. The field is present if the parent stage has multiplechild nodes. For example, stages for $or expressions or index intersection consume input frommultiple sources.

  • explain.queryPlanner.rejectedPlans
  • Array of candidate plans considered and rejected by thequery optimizer. The array can be empty if there were no othercandidate plans.

executionStats

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

  • Unsharded Collections
  • Sharded Collections

For unsharded collections, explain returns the followingexecutionStats information:

  1. "executionStats" : {
  2.    "executionSuccess" : <boolean>,
  3.    "nReturned" : <int>,
  4.    "executionTimeMillis" : <int>,
  5.    "totalKeysExamined" : <int>,
  6.    "totalDocsExamined" : <int>,
  7.    "executionStages" : {
  8.       "stage" : <STAGE1>
  9.       "nReturned" : <int>,
  10.       "executionTimeMillisEstimate" : <int>,
  11.       "works" : <int>,
  12.       "advanced" : <int>,
  13.       "needTime" : <int>,
  14.       "needYield" : <int>,
  15.       "saveState" : <int>,
  16.       "restoreState" : <int>,
  17.       "isEOF" : <boolean>,
  18.       ...
  19.       "inputStage" : {
  20.          "stage" : <STAGE2>,
  21.          "nReturned" : <int>,
  22.          "executionTimeMillisEstimate" : <int>,
  23.          ...
  24.          "inputStage" : {
  25.             ...
  26.          }
  27.       }
  28.    },
  29.    "allPlansExecution" : [
  30.       {
  31.          "nReturned" : <int>,
  32.          "executionTimeMillisEstimate" : <int>,
  33.          "totalKeysExamined" : <int>,
  34.          "totalDocsExamined" :<int>,
  35.          "executionStages" : {
  36.             "stage" : <STAGEA>,
  37.             "nReturned" : <int>,
  38.             "executionTimeMillisEstimate" : <int>,
  39.             ...
  40.             "inputStage" : {
  41.                "stage" : <STAGEB>,
  42.                ...
  43.                "inputStage" : {
  44.                  ...
  45.                }
  46.             }
  47.          }
  48.       },
  49.       ...
  50.    ]
  51. }

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

  1. "executionStats" : {
  2.    "nReturned" : <int>,
  3.    "executionTimeMillis" : <int>,
  4.    "totalKeysExamined" : <int>,
  5.    "totalDocsExamined" : <int>,
  6.    "executionStages" : {
  7.       "stage" : <STAGE1>
  8.       "nReturned" : <int>,
  9.       "executionTimeMillis" : <int>,
  10.       "totalKeysExamined" : <int>,
  11.       "totalDocsExamined" : <int>,
  12.       "totalChildMillis" : <NumberLong>,
  13.       "shards" : [
  14.          {
  15.             "shardName" : <string>,
  16.             "executionSuccess" : <boolean>,
  17.             "executionStages" : {
  18.                "stage" : <STAGE2>,
  19.                "nReturned" : <int>,
  20.                "executionTimeMillisEstimate" : <int>,
  21.                ...
  22.                "chunkSkips" : <int>,
  23.                "inputStage" : {
  24.                   "stage" : <STAGE3>,
  25.                   ...
  26.                   "inputStage" : {
  27.                      ...
  28.                   }
  29.                }
  30.             }
  31.          },
  32.          ...
  33.       ]
  34.    }
  35.    "allPlansExecution" : [
  36.       {
  37.          "shardName" : <string>,
  38.          "allPlans" : [
  39.             {
  40.                "nReturned" : <int>,
  41.                "executionTimeMillisEstimate" : <int>,
  42.                "totalKeysExamined" : <int>,
  43.                "totalDocsExamined" :<int>,
  44.                "executionStages" : {
  45.                   "stage" : <STAGEA>,
  46.                   "nReturned" : <int>,
  47.                   "executionTimeMillisEstimate" : <int>,
  48.                   ...
  49.                   "inputStage" : {
  50.                      "stage" : <STAGEB>,
  51.                      ...
  52.                      "inputStage" : {
  53.                        ...
  54.                      }
  55.                   }
  56.                }
  57.             },
  58.             ...
  59.          ]
  60.       },
  61.       {
  62.          "shardName" : <string>,
  63.          "allPlans" : [
  64.           ...
  65.          ]
  66.       },
  67.       ...
  68.    ]
  69. }
  • explain.executionStats

  • Contains statistics that describe the completed query execution forthe winning plan. For write operations, completed query executionrefers to the modifications that would be performed, but doesnot apply the modifications to the database.

    • explain.executionStats.nReturned

    • Number of documents that match the query condition.nReturned corresponds to the n fieldreturned by cursor.explain() in earlier versions of MongoDB.

    • explain.executionStats.executionTimeMillis

    • Total time in milliseconds required for query plan selection andquery execution. executionTimeMillis correspondsto the millis field returned by cursor.explain() inearlier versions of MongoDB.

    • explain.executionStats.totalKeysExamined

    • Number of index entries scanned.totalKeysExamined corresponds to thenscanned field returned by cursor.explain() inearlier versions of MongoDB.

    • explain.executionStats.totalDocsExamined

    • Number of documents examined during query execution. Commonquery execution stages that examine documents are COLLSCANand FETCH.

Note

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

If a document is examined multiple times during queryexecution,totalDocsExamined countseach examination. That is,totalDocsExamined is not acount of the total number of unique documents examined.

  • explain.executionStats.executionStages
  • Details the completed execution of the winning plan as a tree ofstages; i.e. a stage can have an inputStage or multipleinputStages.

Each stage consists of execution information specific to thestage.

  1. - <code>explain.executionStats.executionStages.</code><code>works</code>[]($b09beaeefc00b1c7.md#explain.executionStats.executionStages.works)
  2. -

Specifies the number of “work units” performed by the queryexecution stage. Query execution divides its work into smallunits. A “work unit” might consist of examining a single index key,fetching a single document from the collection, applying aprojection to a single document, or doing a piece of internalbookkeeping.

  1. - <code>explain.executionStats.executionStages.</code><code>advanced</code>[]($b09beaeefc00b1c7.md#explain.executionStats.executionStages.advanced)
  2. -

The number of intermediate results returned, or advanced, bythis stage to its parent stage.

  1. - <code>explain.executionStats.executionStages.</code><code>needTime</code>[]($b09beaeefc00b1c7.md#explain.executionStats.executionStages.needTime)
  2. -

The number of work cycles that did not advance an intermediateresult to its parent stage (seeexplain.executionStats.executionStages.advanced). Forinstance, an index scan stage may spend a work cycle seeking to anew position in the index as opposed to returning an indexkey; this work cycle would count towardsexplain.executionStats.executionStages.needTime ratherthan explain.executionStats.executionStages.advanced.

  1. - <code>explain.executionStats.executionStages.</code><code>needYield</code>[]($b09beaeefc00b1c7.md#explain.executionStats.executionStages.needYield)
  2. -

The number of times that the storage layer requested that thequery stage suspend processing and yield its locks.

  1. - <code>explain.executionStats.executionStages.</code><code>saveState</code>[]($b09beaeefc00b1c7.md#explain.executionStats.executionStages.saveState)
  2. -

The number of times that the query stage suspended processingand saved its current execution state, for example inpreparation for yielding its locks.

  1. - <code>explain.executionStats.executionStages.</code><code>restoreState</code>[]($b09beaeefc00b1c7.md#explain.executionStats.executionStages.restoreState)
  2. -

The number of times that the query stage restored a savedexecution state, for example after recovering locks that it hadpreviously yielded.

  1. - <code>explain.executionStats.executionStages.</code><code>isEOF</code>[]($b09beaeefc00b1c7.md#explain.executionStats.executionStages.isEOF)
  2. -

Specifies whether the execution stage has reached end of stream:

  1.   - If <code>true</code> or <code>1</code>, the execution stage has reachedend-of-stream.
  2.   - If <code>false</code> or <code>0</code>, the stage may still have results toreturn. For example, consider a query with a limit whoseexecution stages consists of a <code>LIMIT</code> stage with aninput stage of <code>IXSCAN</code> for the query. If the queryreturns more than the specified limit, the <code>LIMIT</code> stagewill report <code>isEOF: 1</code>, but its underlying <code>IXSCAN</code> stagewill report <code>isEOF: 0</code>.
  3. - <code>explain.executionStats.executionStages.inputStage.</code><code>keysExamined</code>[]($b09beaeefc00b1c7.md#explain.executionStats.executionStages.inputStage.keysExamined)
  4. -

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

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

  1. 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 key5, detect that it is out-of-bounds, and skip to the next key50.

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

  1. - <code>explain.executionStats.executionStages.inputStage.</code><code>docsExamined</code>[]($b09beaeefc00b1c7.md#explain.executionStats.executionStages.inputStage.docsExamined)
  2. -

Specifies the number of documents scanned during thequery execution stage.

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

  1. - <code>explain.executionStats.executionStages.inputStage.</code><code>seeks</code>[]($b09beaeefc00b1c7.md#explain.executionStats.executionStages.inputStage.seeks)
  2. -

New in version 3.4: For index scan (IXSCAN) stages only.

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

  • explain.executionStats.allPlansExecution
  • Contains partial execution information captured during theplan selection phase forboth the winning and rejected plans. The field is present only ifexplain runs in allPlansExecution verbosity mode.

serverInfo

  • Unsharded Collections
  • Sharded Collections

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

  1. "serverInfo" : {
  2.    "host" : <string>,
  3.    "port" : <int>,
  4.    "version" : <string>,
  5.    "gitVersion" : <string>
  6. }

For sharded collections, explain returns theserverInfo for each accessed shard.

  1. "queryPlanner" : {
  2.    ...
  3.    "winningPlan" : {
  4.       "stage" : <STAGE1>,
  5.       "shards" : [
  6.          {
  7.             "shardName" : <string>,
  8.             "connectionString" : <string>,
  9.             "serverInfo" : {
  10.                "host" : <string>,
  11.                "port" : <int>,
  12.                "version" : <string>,
  13.                "gitVersion" : <string>
  14.             },
  15.             ...
  16.          }
  17.          ...
  18.       ]

3.0 Format Change

Starting in MongoDB 3.0, the format and fields of the explainresults have changed from previous versions. The following lists somekey differences.

Collection Scan vs. Index Use

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

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

In previous versions of MongoDB, cursor.explain() returned thecursor 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 versusindex scans, see Analyze Query Performance.

Covered Queries

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

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

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

Index Intersection

For an index intersection plan, theresult will include either an AND_SORTED stage or an AND_HASHstage with an inputStages array thatdetails the indexes; e.g.:

  1. {
  2.    "stage" : "AND_SORTED",
  3.    "inputStages" : [
  4.       {
  5.          "stage" : "IXSCAN",
  6.          ...
  7.       },
  8.       {
  9.          "stage" : "IXSCAN",
  10.          ...
  11.       }
  12.    ]
  13. }

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

$or Expression

If MongoDB uses indexes for an $or expression, the result willinclude the OR stage with aninputStages array thatdetails the indexes; e.g.:

  1. {
  2.    "stage" : "OR",
  3.    "inputStages" : [
  4.       {
  5.          "stage" : "IXSCAN",
  6.          ...
  7.       },
  8.       {
  9.          "stage" : "IXSCAN",
  10.          ...
  11.       },
  12.       ...
  13.    ]
  14. }

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

Sort Stage

If MongoDB can use an index scan to obtain the requested sort order,the result will not include a SORT stage. Otherwise, if MongoDBcannot use the index to sort, the explain result will include aSORT stage.

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