db.collection.explain()
On this page
Description
db.collection.explain()
Important
mongosh Method
This page documents a
mongosh
method. This is not the documentation for database commands or language-specific drivers, such as Node.js.For the database command, see the
explain
command.For MongoDB API drivers, refer to the language-specific MongoDB driver documentation.
Returns information on the query plan for the following methods:
Returns information on
mapReduce()
.To use
db.collection.explain()
, append one of the aforementioned methods todb.collection.explain()
:db.collection.explain().<method(...)> For example,
db.products.explain().remove( { category: "apparel" }, { justOne: true } ) For more examples, see Examples. See also db.collection.explain().help().
The
db.collection.explain()
method has the following parameter:ParameterTypeDescriptionverbosity
stringOptional. Specifies the verbosity mode for the explain output. The mode affects the behavior of
explain()
and determines the amount of information to return. The possible modes are:"queryPlanner"
(Default)"executionStats"
"allPlansExecution"
For backwards compatibility with earlier versions of
cursor.explain()
, MongoDB interpretstrue
as"allPlansExecution"
andfalse
as"queryPlanner"
.For more information on the modes, see Verbosity Modes.
Compatibility
This method 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 all commands, see Unsupported 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
Behavior
Note
Using explain
ignores all existing plan cache entries and prevents
the MongoDB query planner from creating a new plan cache entry.
Verbosity Modes
The behavior of db.collection.explain()
and the amount of
information returned depend on the verbosity
mode.
By default, db.collection.explain()
runs in
queryPlanner
verbosity mode.
MongoDB runs the query optimizer to choose
the winning plan for the operation under evaluation. db.collection.explain()
returns
the queryPlanner
information for the evaluated
method.
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, db.collection.explain()
returns information about
the update or delete operations that would be performed, but does
not apply the modifications to the database.
db.collection.explain()
returns the queryPlanner
and
executionStats
information for the evaluated
method. However, executionStats
does not
provide query execution information for the rejected plans.
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, db.collection.explain()
returns information about
the update or delete operations that would be performed, but does
not apply the modifications to the database.
db.collection.explain()
returns the queryPlanner
and
executionStats
information for the evaluated
method. 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.
Explain and Write Operations
For write operations, db.collection.explain()
returns
information about the write operation that would be performed but does
not actually modify the database.
Restrictions
You cannot run the explain
command/db.collection.explain()
in executionStats
mode
or allPlansExecution
mode for an aggregation pipeline
that contains the $out
stage.
Instead, you can either:
explain()
Mechanics
The db.collection.explain()
method wraps the
explain
command and is the preferred way to run
explain
.
db.collection.explain().find()
is similar to
db.collection.find().explain()
with the
following key differences:
The
db.collection.explain().find()
construct allows for the additional chaining of query modifiers. For list of query modifiers, see db.collection.explain().find().help().The
db.collection.find().explain()
returns a cursor, which requires a call to.next()
, or its alias.finish()
, to return theexplain()
results. If run interactively inmongosh
,mongosh
automatically calls.finish()
to return the results. For scripts, however, you must explicitly call.next()
, or.finish()
, to return the results. For list of cursor-related methods, see db.collection.explain().find().help().
db.collection.explain().aggregate()
is equivalent to passing the
explain option to
the db.collection.aggregate()
method.
help()
To see the list of operations supported by
db.collection.explain()
, run:
db.collection.explain().help()
db.collection.explain().find()
returns a cursor, which allows for the chaining of query modifiers.
To see the list of query modifiers supported by
db.collection.explain().find()
as
well as cursor-related methods, run:
db.collection.explain().find().help()
You can chain multiple modifiers to db.collection.explain().find()
.
For an example, see Explain find()
with Modifiers.
Output
db.collection.explain()
operations can return information regarding:
explainVersion
, the output format version (for example,"1"
).command
, which details the command being explained.queryPlanner
, which details the plan selected by the query 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 include
executionStats
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.
Examples
queryPlanner
Mode
By default, db.collection.explain()
runs in
"queryPlanner"
verbosity mode.
The following example runs db.collection.explain()
in
"queryPlanner"
verbosity mode to return the query planning information
for the specified count()
operation:
db.products.explain().count( { quantity: { $gt: 50 } } )
executionStats
Mode
The following example runs db.collection.explain()
in
"executionStats"
verbosity mode
to return the query planning and execution information for the
specified find()
operation:
db.products.explain("executionStats").find( { quantity: { $gt: 50 }, category: "apparel" } )
allPlansExecution
Mode
The following example runs db.collection.explain()
in
"allPlansExecution"
verbosity mode.
db.collection.explain()
returns the
queryPlanner
and executionStats
for
all considered plans for the specified
findAndModify()
operation:
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 the plan selection phase.
db.products.explain( "allPlansExecution" ).findAndModify( { query: { name: "Tom", state: "active", rating: { $gt: 10 } }, sort: { rating: 1 }, update: { $inc: { score: 1 } } } )
Explain find()
with Modifiers
db.collection.explain().find()
construct allows for the chaining of query modifiers. For example, the
following operation provides information on the
find()
method with
sort()
and hint()
query modifiers.
db.products.explain("executionStats").find( { quantity: { $gt: 50 }, category: "apparel" } ).sort( { quantity: -1 } ).hint( { category: 1, quantity: -1 } )
For a list of query modifiers available, run the following in
mongosh
:
db.collection.explain().find().help()
Iterate the explain().find()
Return Cursor
db.collection.explain().find()
returns a cursor to the explain results. If run interactively in
mongosh
, mongosh
automatically
iterates the cursor using the .next()
method. For scripts, however,
you must explicitly call .next()
(or its alias .finish()
) to
return the results:
var explainResult = db.products.explain().find( { category: "apparel" } ).next();