explain
Definition
New in version 3.0.
The explain
command provides information on theexecution of the following commands: aggregate
,count
, distinct
, find
,findAndModify
, delete
, andupdate
.
Although MongoDB provides the explain
command, thepreferred method for running explain
is to use thedb.collection.explain()
and cursor.explain()
helpers.
The explain
command has the following syntax:
- {
- explain: <command>,
- verbosity: <string>
- }
The command takes the following fields:
FieldTypeDescriptionexplain
documentA document specifying the command for which to return the executioninformation. For details on the specific command document, see aggregate
, count
,distinct
, find
,findAndModify
, delete
, and update
.verbosity
stringOptional. A string specifying the mode in which to run explain
.The mode affects the behavior of explain
and determinesthe amount of information to return.
The possible modes are:
"queryPlanner"
"executionStats"
"allPlansExecution"
(Default)For more information on the modes, see explain behavior.
Behavior
Verbosity Modes
The behavior of explain
and the amount of informationreturned depend on the verbosity
mode.
- queryPlanner Mode
- executionStats Mode
- allPlansExecution Mode (Default)
MongoDB runs the query optimizer to choosethe winning plan for the operation under evaluation. explain
returnsthe queryPlanner
information for the evaluated<command>
.
MongoDB runs the query optimizer to choosethe winning plan, executes the winning plan to completion, and returnsstatistics describing the execution of the winning plan.
For write operations, explain
returns information aboutthe update or delete operations that would be performed, but doesnot apply the modifications to the database.
explain
returns the queryPlanner
andexecutionStats
information for the evaluated<command>
. However, executionStats
does notprovide 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 thewinning plan to completion. In "allPlansExecution"
mode, MongoDBreturns statistics describing the execution of the winning plan as wellas statistics for the other candidate plans captured during planselection.
For write operations, explain
returns information aboutthe update or delete operations that would be performed, but doesnot apply the modifications to the database.
explain
returns the queryPlanner
andexecutionStats
information for the evaluated<command>
. The executionStats
includes thecompleted 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 rejectedcandidate plans.
Explain and Write Operations
For write operations, the explain
command returnsinformation about the write operation that would be performed but doesnot actually modify the database.
Restrictions
Starting in MongoDB 4.2, you cannot run the explain
command/db.collection.explain()
in executionStats
modeor 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 orallPlansExecution
mode but without the$out
stage to return informationfor the stages that precede the$out
stage.
Examples
queryPlanner Mode
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"
- }
- )
executionStats Mode
The following explain
operation runs in "executionStats"
verbosity mode to return the query planning and execution informationfor a count
command:
- db.runCommand(
- {
- explain: { count: "products", query: { quantity: { $gt: 50 } } },
- verbosity: "executionStats"
- }
- )
allPlansExecution Mode
By default, explain
runs in "allPlansExecution"
verbositymode. The following explain
command returns thequeryPlanner
and executionStats
forall considered plans for an update
command:
Note
The execution of this explain will not modify data but runs thequery 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 } }
- }
- ]
- }
- }
- )
Output
explain
operations can return information regarding:
- queryPlanner, which details the plan selected by thequery optimizer and lists the rejectedplans;
- executionStats, which details the execution of the winningplan and the rejected plans; and
- serverInfo, which provides information on theMongoDB instance.
The verbosity mode (i.e. queryPlanner
, executionStats
,allPlansExecution
) determines whether the results includeexecutionStats and whether executionStats includes datacaptured during plan selection.
For details on the output, see Explain Results.