explain (original) (raw)

explain

The explain command provides information on the execution of the following commands: aggregate,count, distinct, find,findAndModify, delete,mapReduce, and update.

Tip

In mongosh, this command can also be run through the db.collection.explain() andcursor.explain() helper methods.

Helper methods are convenient for mongosh users, but they may not return the same level of information as database commands. In cases where the convenience is not needed or the additional return fields are required, use the database command.

Note

Using explain ignores all existing plan cache entries and prevents the MongoDB query planner from creating a new plan cache entry.

This command is available in deployments hosted in the following environments:

MongoDB Atlas: The fully managed service for MongoDB deployments in the cloud

Note

This command is supported in all MongoDB Atlas clusters. For information on Atlas support for all commands, seeUnsupported Commands.

MongoDB Enterprise: The subscription-based, self-managed version of MongoDB
MongoDB Community: The source-available, free-to-use, and self-managed version of MongoDB

To use explain, you must have permission to run the underlying command.

The command has the following syntax:


db.runCommand(

   {

     explain: <command>,

     verbosity: <string>,

     comment: <any>

   }

)

The command takes the following fields:

Field	Type	Description
explain	document	A document specifying the command for which to return the execution information. For details on the specific command document, see aggregate, count,distinct, find,findAndModify, delete, mapReduce, and update.
verbosity	string	Optional. A string specifying the mode in which to run explain. The mode affects the behavior of explain and determines the amount of information to return.The possible modes are:"queryPlanner""executionStats""allPlansExecution" (Default)For more information on the modes, see explain behavior.
comment	any	Optional. A user-provided comment to attach to this command. Once set, this comment appears alongside records of this command in the following locations:mongod log messages, in theattr.command.cursor.comment field.Database profiler output, in the command.comment field.currentOp output, in the command.comment field.A comment can be any valid BSON type(string, integer, object, array, etc).If you specify explain without a comment, it inherits any comment in the command specified to explain.

The behavior of explain and the amount of information returned depend on the verbosity mode.

MongoDB runs the query optimizer to choose the winning plan for the operation under evaluation. explain returns the queryPlanner information for the evaluated<command>.

MongoDB runs the query optimizer to choose the winning plan, executes the winning plan to completion, and returns statistics describing the execution of the winning plan.

For write operations, explain returns information about the update or delete operations that would be performed, but does_not_ apply the modifications to the database.

explain returns the queryPlanner andexecutionStats information for the evaluated<command>. However, executionStats does not provide query execution information for the rejected plans.

By default, explain runs in"allPlansExecution" verbosity mode.

MongoDB runs the query optimizer to choose the winning plan and executes the winning plan to completion. In "allPlansExecution" mode, MongoDB returns statistics describing the execution of the winning plan as well as statistics for the other candidate plans captured during plan selection.

For write operations, explain returns information about the update or delete operations that would be performed, but does_not_ apply the modifications to the database.

explain returns the queryPlanner andexecutionStats information for the evaluated<command>. The executionStats includes the_completed_ query execution information for the winning plan.

If the query optimizer considered more than one plan,executionStats information also includes the _partial_execution information captured during the plan selection phase for both the winning and rejected candidate plans.

For write operations, the explain command returns information about the write operation that would be performed but does not actually modify the database.

The Stable API V1 supports the following verbosity modes for the explain command:

allPlansExecution
executionStats
queryPlanner

Warning

MongoDB does not guarantee any specific output format from theexplain command, even when using the Stable API.

You cannot run the explaincommand/db.collection.explain() in executionStats mode or allPlansExecution mode for an aggregation pipeline that contains the $out stage. Instead, you can either:

run the explain in queryPlanner mode or
run the explain in executionStats mode or allPlansExecutionmode but without the $out stage to return information for the stages that precede the $out stage.

explain operations can return information regarding:

explainVersion, the output format version (for example, "1").
command, which details the command being explained.
queryShapeHash, starting in MongoDB 8.0, which is a hexadecimal string with the hash of a query shape. For details, seeQuery Shapes, Query Shape Hash, andexplain.queryShapeHash.
queryPlanner, which details the plan selected by thequery optimizer and lists the rejected plans.
executionStats, which details the execution of the winning plan and the rejected plans.
serverInfo, which provides information on the MongoDB instance.
serverParameters, which details internal parameters.

The verbosity mode (i.e. queryPlanner, executionStats,allPlansExecution) determines whether the results includeexecutionStats and whether executionStats includes data captured during plan selection.

Explain output is limited by the maximum Nested Depth for BSON Documents, which is 100 levels of nesting. Explain output that exceeds the limit is truncated.

For details on the output, see Explain Results.

The following explain command runs in "queryPlanner"verbosity mode to return the query planning information for acount command:


db.runCommand(

   {

     explain: { count: "products", query: { quantity: { $gt: 50 } } },

     verbosity: "queryPlanner"

   }

)

The following explain operation runs in "executionStats"verbosity mode to return the query planning and execution information for a count command:


db.runCommand(

   {

      explain: { count: "products", query: { quantity: { $gt: 50 } } },

      verbosity: "executionStats"

   }

)

By default, explain runs in "allPlansExecution" verbosity mode. The following explain command returns thequeryPlanner and executionStats for all considered plans for an update command:

Note

The execution of this explain will not modify data but runs the query predicate of the update operation. For candidate plans, MongoDB returns the execution information captured during theplan selection phase.


db.runCommand(

   {

     explain: {

        update: "products",

        updates: [

           {

               q: { quantity: 1057, category: "apparel" },

               u: { $set: { reorder: true } }

           }

        ]

     }

   }

)