Explain Results

queryPlanner

explain.queryPlanner information details the plan selected by the query optimizer.

These examples may combine the output structures of MongoDB's classic and slot-based execution engines. They are not meant to be representative. Your output may differ significantly.

queryPlanner: {
   namespace: <string>,
   indexFilterSet: <boolean>,
   parsedQuery: {
      ...
   },
   planCacheShapeHash: <hexadecimal string>,
   planCacheKey: <hexadecimal string>,
   maxIndexedOrSolutionsReached: <boolean>,
   maxIndexedAndSolutionsReached: <boolean>,
   maxScansToExplodeReached: <boolean>,
   winningPlan: {
     stage: <STAGE1>,
     type: <string>,
     inputStage: {
       stage: <string>,
       ...
     }
   },
   rejectedPlans: [
     <candidate plan1>,
   ]
}

{
   queryPlanner: {
      mongosPlannerVersion: <int>
      winningPlan: {
         stage: <STAGE1>,
         type: <string>,
         shards: [
         {
            shardName: <string>,
            connectionString: <string>,
            serverInfo: {
               ...
            },
            namespace: <string>,
            indexFilterSet: <boolean>,
            parsedQuery: {
               ...
            },
            querySettings: {
               ...
            },
            planCacheShapeHash: <hexadecimal string>,
            planCacheKey: <hexadecimal string>,
            maxIndexedOrSolutionsReached: <boolean>,
            maxIndexedAndSolutionsReached: <boolean>,
            maxScansToExplodeReached: <boolean>,
            winningPlan: {
               stage: <STAGE1>,
               type: <string>,
               inputStage: {
                  stage: <string>,
                  ...
               }
            },
            rejectedPlans: [
              <candidate plan1>,
            ]
         }
         ]
      }
   }
}

explain.queryPlanner

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

explain.queryPlanner.namespace

A string that specifies the namespace with the names of the database and the collection accessed by the query. The namespace has the format <database>.<collection>.

explain.queryPlanner.indexFilterSet

A boolean that specifies whether MongoDB applied an index filter for the plan cache query shape.

explain.queryPlanner.querySettings

If query settings are set, querySettings contains details about the query settings that are applied to the query shape.

To add query settings and explore examples, which include explain() output with querySettings, see setQuerySettings.

New in version 8.0.

explain.queryPlanner.planCacheShapeHash

Starting in MongoDB 8.0, the existing queryHash field is duplicated in a new field named planCacheShapeHash. If you're using an earlier MongoDB version, you'll only see the queryHash field. Future MongoDB versions will remove the deprecated queryHash field, and you'll need to use the planCacheShapeHash field instead.

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

Note

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

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

explain.queryPlanner.planCacheKey

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

Unlike the explain.queryPlanner.planCacheShapeHash, the explain.queryPlanner.planCacheKey is a function of both the plan cache query shape and the currently available indexes for that shape. Specifically, if indexes that can support the query shape are added or dropped, the planCacheKey value may change but the planCacheShapeHash value wouldn't change.

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

explain.queryPlanner.optimizationTimeMillis

Time in milliseconds the query planner spent on query optimization. This result doesn't include time spent optimizing inner $lookup queries.

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, the following aggregation operation can be fulfilled by the tree of query plan execution rather than using the aggregation pipeline.

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.

explain.queryPlanner.winningPlan

A document that details the plan selected by the query optimizer.

explain.queryPlanner.winningPlan.stage

A string that denotes the name of the stage.

Each stage consists of information specific to the stage. For example, an IXSCAN stage includes 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.

This field appears if the operation used the classic query execution engine.

explain.queryPlanner.winningPlan.type

If stage is set to "EOF", type is a string that denotes the reason for the EOF stage:

• "nonExistentNamespace": indicates the query namespace does not exist.

• "predicateEvalsToFalse": indicates that expression simplification determines that the query will not match any documents.

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 might consume input from multiple sources.

This field appears if the operation used the classic query execution engine.

explain.queryPlanner.winningPlan.isCached

A boolean that indicates if the winningPlan is in the plan cache.

This field is true for a maximum of one plan between the winning plan and the rejected plans. If the query has no cached plan, the field is false for the winning plan and the rejected plans.

explain.queryPlanner.winningPlan.queryPlan

A document that details the plan selected by the query optimizer. MongoDB presents the plan as a tree of stages.

This document appears if the query used the slot-based execution query engine.

New in version 5.1.

explain.queryPlanner.winningPlan.queryPlan.stage

A string that denotes the name of the stage.

Each stage consists of information specific to the stage. For example, an IXSCAN stage includes the index bounds along with other data specific to the index scan.

explain.queryPlanner.winningPlan.queryPlan.type

If stage is set to "EOF", type is a string that denotes the reason for the EOF stage:

• "nonExistentNamespace": indicates the query namespace does not exist.

• "predicateEvalsToFalse": indicates that expression simplification determines that the query will not match any documents.

explain.queryPlanner.winningPlan.queryPlan.planNodeId

Unique integer field that identifies each stage in the execution plan. Field is included in all stages throughout the explain results.

New in version 5.1.

explain.queryPlanner.winningPlan.queryPlan.inputStage

See explain.queryPlanner.winningPlan.inputStage.

explain.queryPlanner.winningPlan.slotBasedPlan

Document with information about the slot-based query execution plan tree and stages. For internal use by MongoDB.

New in version 5.1.

explain.queryPlanner.winningPlan.slotBasedPlan.stages

String that includes information on each stage in the slot-based query execution plan, including execution order, operations, and slot assignments. For internal use by MongoDB.

Starting in MongoDB 8.0, if a query uses block processing, block_to_row and ts_bucket_to_cellblock appear in the stages output.

Changed in version 8.0.

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.

Rejected plans have the same fields as explain.queryPlanner.winningPlan.

Starting in MongoDB 8.0, rejected query plans only contain the find portion of the query. In previous versions, rejected plans can contain aggregation stages like $group. Those aggregation stages aren't used by the query planner to choose the winning plan, so the rejectedPlans field only contains the portion of the query that was used to choose the winning plan.

executionStats

The returned explain.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.

These examples may combine the output structures of MongoDB's classic and slot-based execution engines. They are not meant to be representative. Your output may differ significantly.

executionStats: {
   executionSuccess: <boolean>,
   nReturned: <int>,
   executionTimeMillis: <int>,
   totalKeysExamined: <int>,
   totalDocsExamined: <int>,
   executionStages: {
      stage: <STAGE1>
      nReturned: <int>,
      executionTimeMillisEstimate: <int>,
      opens: <int>, // Starting in MongoDB 5.1
      closes: <int>, // Starting in MongoDB 5.1
      works: <int>,
      advanced: <int>,
      needTime: <int>,
      needYield: <int>,
      saveState: <int>,
      restoreState: <int>,
      isEOF: <boolean>,
      ...
      inputStage: {
         stage: <STAGE2>,
         nReturned: <int>,
         ...
         numReads: <int>, // Starting in MongoDB 5.1
         ...
         executionTimeMillisEstimate: <int>,
         ...
         spills: <long long>,
         spilledBytes: <long long>,
         spilledRecords: <long long>,
         spilledDataStorageSize: <long long>,
         ...
         inputStage: {
            ...
         }
      }
   },
   allPlansExecution: [
      {
         nReturned: <int>,
         executionTimeMillisEstimate: <int>,
         totalKeysExamined: <int>,
         totalDocsExamined:<int>,
         executionStages: {
            stage: <STAGEA>,
            nReturned: <int>,
            executionTimeMillisEstimate: <int>,
            ...
            inputStage: {
               stage: <STAGEB>,
               ...
               inputStage: {
                 ...
               }
            }
         }
      },
      ...
   ]
   operationMetrics: {
      cpuNanos: <int>,
      cursorSeeks: <int>,
      docBytesRead: <int>,
      docBytesWritten: <int>,
      docUnitsRead: <int>,
      docUnitsReturned: <int>,
      docUnitsWritten: <int>,
      idxEntryBytesRead: <int>,
      idxEntryBytesWritten: <int>,
      idxEntryUnitsRead: <int>,
      idxEntryUnitsWritten: <int>,
      totalUnitsWritten: <int>,
      keysSorted: <int>,
      sorterSpills: <int>
   }
}

executionStats: {
   nReturned: <int>,
   executionTimeMillis: <int>,
   totalKeysExamined: <int>,
   totalDocsExamined: <int>,
   executionStages: {
      stage: <STAGE1>
      nReturned: <int>,
      executionTimeMillis: <int>,
      opens: <int>, // Starting in MongoDB 5.1
      closes: <int>, // Starting in MongoDB 5.1
      totalKeysExamined: <int>,
      totalDocsExamined: <int>,
      totalChildMillis: <NumberLong>,
      shards: [
         {
            shardName: <string>,
            executionSuccess: <boolean>,
            executionStages: {
               stage: <STAGE2>,
               nReturned: <int>,
               executionTimeMillisEstimate: <int>,
               ...
               chunkSkips: <int>,
               inputStage: {
                  stage: <STAGE3>,
                  ...
                  numReads: <int>, // Starting in MongoDB 5.1
                  ...
                  spills: <long long>,
                  spilledBytes: <long long>,
                  spilledRecords: <long long>,
                  spilledDataStorageSize: <long long>,
                  ...
                  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 returned by the winning query plan. 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. It includes the time it takes to run the trial phase part of the plan selection process, but does not include the network time to transmit the data back to the client.

The time reported by explain.executionStats.executionTimeMillis is not necessarily representative of actual query time. During steady state operations (when the query plan is cached), or when using cursor.hint() with cursor.explain(), MongoDB bypasses the plan selection process, resulting in a faster actual time, leading to a lower explain.executionStats.executionTimeMillis value.

explain.executionStats.totalKeysExamined

Number of index entries scanned. explain.executionStats.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

explain.executionStats.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, explain.executionStats.totalDocsExamined counts each examination. That is, explain.executionStats.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.

Starting in MongoDB 5.1, a stage can have these input stages:

• thenStage

• elseStage

• innerStage

• outerStage

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

Starting in MongoDB 5.1, the number of times a stage was opened during query execution.

explain.executionStats.executionStages.closes

Starting in MongoDB 5.1, the number of times a stage was closed during 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.

This field appears if the operation used the classic query execution engine.

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

For operations on time series collections, the type of operation.

explain.executionStats.executionStages.bucketFilter

For operations on time series collections, the filter used to reduce the number of buckets queried in the bucket catalog.

explain.executionStats.executionStages.residualFilter

For operations on time series collections, the filter used to query the bucket catalog.

explain.executionStats.executionStages.nBucketsUnpacked

For operations on time series collections, the number of buckets unpacked to resolve the operation.

explain.executionStats.executionStages.nMeasurementsDeleted

For operations on time series collections, the number of documents deleted.

explain.executionStats.executionStages.nMeasurementsInserted

For operations on time series collections, the number of documents inserted.

explain.executionStats.executionStages.nMeasurementsMatched

For operations on time series collections, the number of documents matched.

explain.executionStats.executionStages.nMeasurementsModified

For operations on time series collections, the number of documents modified.

explain.executionStats.executionStages.nMeasurementsUpserted

For operations on time series collections, the number of documents upserted.

explain.executionStats.executionStages.inputStage

Each inputStage can have different fields depending on the value of inputStage.stage. The following table describes possible fields and what stages they can appear in.

Each inputStage can have another inputStage as a field. See Explain Output Structure.

Field	Description	Applicable Stages
docsExamined	Specifies the number of documents scanned during the query execution stage.	COLLSCAN, FETCH
keysExamined	For query execution stages that scan an index 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.	IXSCAN
numReads	The number of documents scanned or index keys examined during the query execution stage. New in version 5.1.	COLLSCAN, IXSCAN
seeks	The number of times that we had to seek the index cursor to a new position in order to complete the index scan.	IXSCAN
usedDisk	Whether the stage wrote to disk. New in version 5.3.	GROUP
spills	The number of times the stage spilled to disk. New in version 8.2.	GROUP, SORT
spilledBytes	The total amount of data written to disk in bytes. New in version 8.2.	GROUP, SORT
spilledRecords	The total number of individual records spilled to disk. New in version 8.2.	GROUP, SORT
spilledDataStorageSize	The total disk space, in bytes, used by the spilled data. New in version 8.2.	GROUP, SORT

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.

explain.executionStats.operationMetrics

Contains resource consumption statistics, as long as they are not zero. The field is present only if explain runs in executionStats verbosity mode or higher and if profileOperationResourceConsumptionMetrics is enabled.

Warning

MongoDB 8.2 removes profileOperationResourceConsumptionMetrics. Therefore, code depending on operationMetrics does not work.

serverInfo

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

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