- Reference >
- mongo Shell Methods >
- Collection Methods >
- db.collection.explain()
db.collection.explain()¶
On this page
Description¶
- db.collection.explain()¶
Changed in version 3.2: Adds support for db.collection.distinct()
New in version 3.0.
Returns information on the query plan for the following operations: aggregate(); count(); distinct(); find(); group(); remove(); and update() methods.
To use db.collection.explain(), append to db.collection.explain() the method(s) available to explain:
db.collection.explain().<method(...)>
For example,
db.products.explain().remove( { category: "apparel" }, { justOne: true } )
For more examples, see Examples. For a list of methods available for use with db.collection.explain(), see 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", "executionStats", and "allPlansExecution".
Default mode is "queryPlanner".
For backwards compatibility with earlier versions of cursor.explain(), MongoDB interprets true as "allPlansExecution" and false as "queryPlanner".
aggregate() ignores the verbosity parameter and executes in queryPlanner mode.
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.
aggregate() ignores the verbosity parameter and executes in queryPlanner mode.
queryPlanner 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.
executionStats Mode¶
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.
allPlansExecution 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, 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() 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.explain().find() returns a cursor, which requires a call to .next(), or its alias .finish(), to return the explain() results. If run interactively in the mongo shell, the mongo shell 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.
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. The db.collection.explain() returns the queryPlanner and executionStats for all considered plans for the specified update() 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").update(
{ quantity: { $lt: 1000}, category: "apparel" },
{ $set: { reorder: true } }
)
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 in the mongo shell:
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 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:
var explainResult = db.products.explain().find( { category: "apparel" } ).next();
Output¶
db.collection.explain() operations can return information regarding:
- 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; and
- serverInfo, which provides information on the MongoDB instance.
The verbosity mode (i.e. queryPlanner, executionStats, allPlansExecution) determines whether the results include executionStats and whether executionStats includes data captured during plan selection.
For details on the output, see Explain Results.
For a mixed version sharded cluster with version 3.0 mongos and at least one 2.6 mongod shard, when you run db.collection.explain() in a version 3.0 mongo shell, db.collection.explain() will retry with the $explain operator to return results in the 2.6 format.