- Reference >
mongo
Shell Methods >- Cursor Methods >
- cursor.explain()
cursor.explain()¶
On this page
Definition¶
-
cursor.
explain
(verbose)¶ Provides information on the query plan. The query plan is the plan the server uses to find the matches for a query. This information may be useful when optimizing a query. The
explain()
method returns a document that describes the process used to return the query results.The
explain()
method has the following form:The
explain()
method has the following parameter:Parameter Type Description verbose
Boolean Optional. Specifies the level of detail to include in the output. If true
or1
, includes theallPlans
andoldPlan
fields in the output.For an explanation of output, see Explain on Queries on Sharded Collections and Core Explain Output Fields.
Behavior¶
The explain()
method runs the actual query to
determine the result. Although there are some differences between
running the query with explain()
and
running without, generally, the performance will be similar between
the two. So, if the query is slow, the explain()
operation is also slow.
Additionally, the explain()
operation reevaluates a set
of candidate query plans, which may cause the explain()
operation to perform differently than a normal query. As a result,
these operations generally provide an accurate account of how
MongoDB would perform the query, but do not reflect the length of
these queries.
Explain Results¶
Explain on Queries on Unsharded Collections¶
For queries on unsharded collections, explain()
returns the following core information.
For details on the fields, see Core Explain Output Fields.
Explain on $or
Queries¶
Queries with the $or
operator can use separate indexes on
each clause of the $or
expression. If the query is indexed,
explain()
contains
output for each clause as well
as the cumulative data for the entire query:
For details on the fields, see $or Query Output Fields and Core Explain Output Fields.
Explain on Queries on Sharded Collections¶
For queries on sharded collections, explain()
returns
information for each shard the query accesses. For queries on
unsharded collections, see Core Explain Output Fields.
For queries on a sharded collection, the output contains the Core Explain Output Fields for each accessed shard and cumulative shard information:
For details on these fields, see Core Explain Output Fields for each accessed shard and Sharded Collections Output Fields.
Explain Output Fields¶
Core Explain Output Fields¶
This section explains output for queries on collections that are not sharded. For queries on sharded collections, see Explain on Queries on Sharded Collections.
-
explain.
cursor
¶ cursor
is a string that reports the type of cursor used by the query operation:BasicCursor
indicates a full collection scan.BtreeCursor
indicates that the query used an index. The cursor includes name of the index. When a query uses an index, the output ofexplain()
includesindexBounds
details.GeoSearchCursor
indicates that the query used a geospatial index.Complex Plan
indicates that MongoDB used index intersection.
For
BtreeCursor
cursors, MongoDB will append the name of the index to the cursor string. Additionally, depending on how the query uses an index, MongoDB may append one or both of the following strings to the cursor string:reverse
indicates that query transverses the index from the highest values to the lowest values (e.g. “right to left”.)multi
indicates that the query performed multiple look-ups. Otherwise, the query uses the index to determine a range of possible matches.
-
explain.
isMultiKey
¶ isMultiKey
is a boolean. Whentrue
, the query uses a multikey index, where one of the fields in the index holds an array.
-
explain.
nscanned
¶ nscanned
is the total number of index entries scanned (or documents for a collection scan).nscanned
can be greater thannscannedObjects
, which is the total number of documents scanned during the query. For examples, see Covered Query Explain Output, Multikey Index Explain Output, and Compare Performance of Indexes.
-
explain.
nscannedObjects
¶ nscannedObjects
is the total number of documents scanned.nscannedObjects
can be lower thannscanned
. For examples, see Covered Query Explain Output, Multikey Index Explain Output, and Compare Performance of Indexes.
-
explain.
nscannedObjectsAllPlans
¶ New in version 2.2.
nscannedObjectsAllPlans
is a number that reflects the total number of documents scanned for all query plans during the database operation.
-
explain.
nscannedAllPlans
¶ New in version 2.2.
nscannedAllPlans
is a number that reflects the total number of documents or index entries scanned for all query plans during the database operation.
-
explain.
scanAndOrder
¶ scanAndOrder
is a boolean that istrue
when the query cannot use the order of documents in the index for returning sorted results: MongoDB must sort the documents after it receives the documents from a cursor.If
scanAndOrder
isfalse
, MongoDB can use the order of the documents in an index to return sorted results.
-
explain.
indexOnly
¶ indexOnly
is a boolean value that returnstrue
when the query is covered by the index indicated in thecursor
field. When an index covers a query, MongoDB can both match the query conditions and return the results using only the index because:- all the fields in the query are part of that index, and
- all the fields returned in the results set are in the same index.
-
explain.
nYields
¶ nYields
is a number that reflects the number of times this query yielded the read lock to allow waiting writes to execute.
-
explain.
nChunkSkips
¶ nChunkSkips
is a number that reflects the number of documents skipped because of active chunk migrations in a sharded system. Typically this will be zero. A number greater than zero is ok, but indicates a little bit of inefficiency.
-
explain.
indexBounds
¶ indexBounds
is a document that contains the lower and upper index key bounds. This field resembles one of the following:
-
explain.
allPlans
¶ allPlans
is an array that holds the list of plans the query optimizer runs in order to select the index for the query. Displays only when the<verbose>
parameter toexplain()
istrue
or1
.
-
explain.
oldPlan
¶ New in version 2.2.
oldPlan
is a document value that contains the previous plan selected by the query optimizer for the query. Displays only when the<verbose>
parameter toexplain()
istrue
or1
.
-
explain.
server
¶ New in version 2.2.
server
is a string that reports the MongoDB server hostname or IP address.
-
explain.
filterSet
¶ New in version 2.6.
filterSet
is a boolean that indicates whether MongoDB applied an index filter for the query.
$or
Query Output Fields¶
Sharded Collections Output Fields¶
-
explain.
clusteredType
¶ clusteredType
is a string that reports the access pattern for shards. The value is:
-
explain.
shards
¶ shards
contains fields for each shard in the cluster accessed during the query. Each field holds the Core Explain Output Fields for that shard.
-
explain.
millisShardTotal
¶ millisShardTotal
is a number that reports the total time in milliseconds for the query to run on the shards.
-
explain.
millisShardAvg
¶ millisShardAvg
is a number that reports the average time in millisecond for the query to run on each shard.
-
explain.
numQueries
¶ numQueries
is a number that reports the total number of queries executed.
Examples¶
Consider a collection survey
with the following documents:
Collection Scan¶
Consider the query:
If no index supports the query, MongoDB must perform a collection scan.
To view the query plan used, use explain()
:
explain()
returns the following output:
cursor
displaysBasicCursor
to indicate a collection scan.n
displays1
to indicate that the query matches and returns one document.- However,
nscanned
andnscannedObjects
display4
to indicate that MongoDB had to scan four documents (i.e. all the documents in the collection) to find the one document that matched.
Index Use¶
Consider the following index on the item
field on the survey
collection:
To view how MongoDB uses the index for the following query on the
item
field, use explain()
:
explain()
returns the following output:
cursor
displaysBtreeCursor item_1
to indicate index use and the name of the index.n
displays1
to indicate that the query matches and returns one document.nscanned
displays1
to indicate that MongoDB scanned one index entry.nscannedObjects
displays1
to indicate that MongoDB scanned one document.
Covered Query Explain Output¶
With covered queries, MongoDB
can use the index to match and return the results; i.e. MongoDB does
not need access the documents. If an index covers a query, nscannedObjects
will
be 0
.
Consider the following query, which specify a query predicate on the
indexed item
field and returns only the indexed item
field:
To view how MongoDB uses the index for the query, use
explain()
:
explain()
returns the following output:
cursor
displaysBtreeCursor item_1
to indicate index use and the name of the index.n
displays1
to indicate that one document matches the query.nscanned
displays1
to indicate that MongoDB scanned one index entry.- However,
nscannedObjects
displays0
to indicate that MongoDB did not need to scan any document. MongoDB used just the index, as verified byindexOnly
. indexOnly
displaystrue
to indicate that MongoDB used only the index to match and return the result of the query.
Multikey Index Explain Output¶
With a multikey index on an array field, MongoDB adds an index entry for each item in the array.
Consider following multi-key index on the ratings
array on the
survey
collection:
And consider the following query:
The query matches a single document:
To view how MongoDB uses the index for the query, use
explain()
:
explain()
returns the following output:
cursor
displaysBtreeCursor ratings_1
to indicate index use and the name of the index.isMultiKey
displaystrue
to indicate that the index is multi-key.n
displays1
to indicate that one document matches the query.nscanned
displays2
to indicate that MongoDB scanned two index entries.- However,
nscannedObjects
displays1
since both keys refer to the same document and MongoDB de-duplicates.