Navigation

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 a language-specific driver such as Node.js.

For MongoDB API drivers, refer to the language-specific :driver:`MongoDB driver documentation </>`.

For the legacy mongo shell documentation, refer to the documentation for the corresponding MongoDB Server release:

Returns information on the query plan for the following methods:

Starting in MongoDB 3.0 Starting in MongoDB 3.2 Starting in MongoDB 4.4

To use db.collection.explain(), append one of the aforementioned methods to db.collection.explain():

copy 
db.collection.explain().<method(...)>

For example,

copy 
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:

Parameter Type Description
verbosity string

Optional. 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 interprets true as "allPlansExecution" and false as "queryPlanner".

For more information on the modes, see Verbosity Modes.

Behavior

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

Starting in MongoDB 4.2, 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:

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

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:

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:

copy 
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:

copy 
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; and
  • 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:

copy 
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:

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

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

copy 
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:

copy 
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:

copy 
var explainResult = db.products.explain().find( { category: "apparel" } ).next();

© MongoDB, Inc 2008-present. MongoDB, Mongo, and the leaf logo are registered trademarks of MongoDB, Inc.