db.collection.explain()

Description

  • db.collection.explain()

mongo Shell Method

This page documents the mongo shell method, and doesnot refer to the MongoDB Node.js driver (or any other driver)method. For corresponding MongoDB driver API, refer to your specificMongoDB driver documentation instead.

Returns information on the query plan for the following methods:

Starting in MongoDB 3.0Starting in MongoDB 3.2

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

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

For example,

  1. db.products.explain().remove( { category: "apparel" }, { justOne: true } )

For more examples, see Examples. See alsodb.collection.explain().help().

The db.collection.explain() method has the followingparameter:

ParameterTypeDescriptionverbositystringOptional. Specifies the verbosity mode for the explain output. The mode affectsthe behavior of explain() and determines the amount of informationto return. The possible modes are:

  • "queryPlanner" (Default)
  • "executionStats"
  • "allPlansExecution"For backwards compatibility with earlier versions ofcursor.explain(), MongoDB interprets true as"allPlansExecution" and false as "queryPlanner".

For more information on the modes, seeVerbosity Modes.

Behavior

Verbosity Modes

The behavior of db.collection.explain() and the amount ofinformation returned depend on the verbosity mode.

  • queryPlanner Mode (Default)
  • executionStats Mode
  • allPlansExecution Mode

By default, db.collection.explain() runs inqueryPlanner verbosity mode.

MongoDB runs the query optimizer to choosethe winning plan for the operation under evaluation. db.collection.explain() returnsthe queryPlanner information for the evaluatedmethod.

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, db.collection.explain() returns information aboutthe update or delete operations that would be performed, but doesnot apply the modifications to the database.

db.collection.explain() returns the queryPlanner andexecutionStats information for the evaluatedmethod. However, executionStats does notprovide query execution information for the rejected plans.

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, db.collection.explain() returns information aboutthe update or delete operations that would be performed, but doesnot apply the modifications to the database.

db.collection.explain() returns the queryPlanner andexecutionStats information for the evaluatedmethod. 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, db.collection.explain() 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 explaincommand/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 or allPlansExecutionmode but without the $out stage to return informationfor the stages that precede the $out stage.

explain() Mechanics

The db.collection.explain() method wraps theexplain command and is the preferred way to runexplain.

db.collection.explain().find() is similar todb.collection.find().explain() with thefollowing key differences:

  • The db.collection.explain().find() construct allows for theadditional chaining of query modifiers. For list of query modifiers,see db.collection.explain().find().help().
  • The db.collection.explain().find() returns a cursor, whichrequires a call to .next(), or its alias .finish(), to returnthe explain() results.If run interactively in the mongo shell, themongo shell automatically calls .finish() to returnthe results. For scripts, however, you must explicitly call.next(), or .finish(), to return the results. For list ofcursor-related methods, seedb.collection.explain().find().help().

db.collection.explain().aggregate() is equivalent to passingthe explain option tothe db.collection.aggregate() method.

help()

To see the list of operations supported bydb.collection.explain(), run:

  1. db.collection.explain().help()

db.collection.explain().find() returns a cursor, which allowsfor the chaining of query modifiers. To see the list of query modifierssupported by db.collection.explain().find() as well as cursor-related methods, run:

  1. db.collection.explain().find().help()

You can chain multiple modifiers to db.collection.explain().find().For an example, see Explain find() with Modifiers.

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 informationfor the specified count() operation:

  1. db.products.explain().count( { quantity: { $gt: 50 } } )

executionStats Mode

The following example runs db.collection.explain() in"executionStats" verbosity modeto return the query planning and execution information for thespecified find() operation:

  1. db.products.explain("executionStats").find(
  2.    { quantity: { $gt: 50 }, category: "apparel" }
  3. )

allPlansExecution Mode

The following example runs db.collection.explain() in"allPlansExecution" verbosity mode. Thedb.collection.explain() returns thequeryPlanner and executionStats forall considered plans for the specifiedupdate() operation:

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.

  1. db.products.explain("allPlansExecution").update(
  2.    { quantity: { $lt: 1000}, category: "apparel" },
  3.    { $set: { reorder: true } }
  4. )

Explain find() with Modifiers

db.collection.explain().find() construct allows for thechaining of query modifiers. For example, the following operationprovides information on the find() method withsort() and hint() query modifiers.

  1. db.products.explain("executionStats").find(
  2.    { quantity: { $gt: 50 }, category: "apparel" }
  3. ).sort( { quantity: -1 } ).hint( { category: 1, quantity: -1 } )

For a list of query modifiers available, run in the mongoshell:

  1. db.collection.explain().find().help()

Iterate the explain().find() Return Cursor

db.collection.explain().find() returns a cursor to theexplain results. If run interactively in the mongo shell,the mongo shell automatically iterates the cursor using the.next() method. For scripts, however, you must explicitly call.next() (or its alias .finish()) to return the results:

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

Output

db.collection.explain() operations can return information regarding:

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.