• Reference >
• mongo Shell Methods >
• Cursor Methods >
• cursor.explain()

cursor.explain()

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:

copy

db.collection.find().explain()

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 or 1, includes the allPlans and oldPlan 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.

copy

{
  "cursor" : "<Cursor Type and Index>",
  "isMultiKey" : <boolean>,
  "n" : <num>,
  "nscannedObjects" : <num>,
  "nscanned" : <num>,
  "nscannedObjectsAllPlans" : <num>,
  "nscannedAllPlans" : <num>,
  "scanAndOrder" : <boolean>,
  "indexOnly" : <boolean>,
  "nYields" : <num>,
  "nChunkSkips" : <num>,
  "millis" : <num>,
  "indexBounds" : { <index bounds> },
  "allPlans" : [
                 { "cursor" : "<Cursor Type and Index>",
                   "n" : <num>,
                   "nscannedObjects" : <num>,
                   "nscanned" : <num>,
                   "indexBounds" : { <index bounds> }
                 },
                  ...
               ],
  "oldPlan" : {
                "cursor" : "<Cursor Type and Index>",
                "indexBounds" : { <index bounds> }
              }
  "server" : "<host:port>",
  "filterSet" : <boolean>
}

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:

copy

{
   "clauses" : [
                  {
                     <core explain output>
                  },
                  {
                     <core explain output>
                  },
                  ...
               ],
   "n" : <num>,
   "nscannedObjects" : <num>,
   "nscanned" : <num>,
   "nscannedObjectsAllPlans" : <num>,
   "nscannedAllPlans" : <num>,
   "millis" : <num>,
   "server" : "<host:port>"
}

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:

copy

{
   "clusteredType" : "<Shard Access Type>",
   "shards" : {

                "<shard1>" : [
                               {
                                 <core explain output>
                               }
                             ],
                "<shard2>" : [
                               {
                                <core explain output>
                               }
                             ],
                ...
              },
   "millisShardTotal" : <num>,
   "millisShardAvg" : <num>,
   "numQueries" : <num>,
   "numShards" : <num>,
   "cursor" : "<Cursor Type and Index>",
   "n" : <num>,
   "nChunkSkips" : <num>,
   "nYields" : <num>,
   "nscanned" : <num>,
   "nscannedAllPlans" : <num>,
   "nscannedObjects" : <num>,
   "nscannedObjectsAllPlans" : <num>,
   "millis" : <num>
}

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 of explain() includes indexBounds 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. When true, the query uses a multikey index, where one of the fields in the index holds an array.

explain.n

n is the number of documents that match the query selection criteria.

explain.nscanned

nscanned is the total number of index entries scanned (or documents for a collection scan).

nscanned can be greater than nscannedObjects, 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 than nscanned. 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 is true 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 is false, MongoDB can use the order of the documents in an index to return sorted results.

explain.indexOnly

indexOnly is a boolean value that returns true when the query is covered by the index indicated in the cursor 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.millis

millis is a number that reflects the time in milliseconds to complete the query.

explain.indexBounds

indexBounds is a document that contains the lower and upper index key bounds. This field resembles one of the following:

copy

"indexBounds" : {
                    "start" : { <index key1> : <value>, ...  },
                    "end" : { <index key1> : <value>, ... }
                },

copy

"indexBounds" : { "<field>" : [ [ <lower bound>, <upper bound> ] ],
                  ...
                }

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 to explain() is true or 1.

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 to explain() is true or 1.

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

explain.clauses

clauses is an array that holds the Core Explain Output Fields information for each clause of the $or expression. clauses is only included when the clauses in the $or expression use indexes.

Sharded Collections Output Fields

explain.clusteredType

clusteredType is a string that reports the access pattern for shards. The value is:

• ParallelSort, if the mongos queries shards in parallel.
• SerialServer, if the mongos queries shards sequentially.

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.

explain.numShards

numShards is a number that reports the total number of shards queried.

Examples

Consider a collection survey with the following documents:

copy

{ "_id" : 1, "item" : "xyz", "ratings" : [ 8, 9, 10 ] }
{ "_id" : 2, "item" : "abc", "ratings" : [ 7 ] }
{ "_id" : 3, "item" : "ijk", "ratings" : [ 6, 5 ] }
{ "_id" : 4, "item" : "lmn", "ratings" : [ 8 ] }

Collection Scan

Consider the query:

copy

db.survey.find( { item: "abc" } )

If no index supports the query, MongoDB must perform a collection scan. To view the query plan used, use explain():

copy

db.survey.find( { item: "abc" } ).explain()

explain() returns the following output:

copy

{
   "cursor" : "BasicCursor",
   "isMultiKey" : false,
   "n" : 1,
   "nscannedObjects" : 4,
   "nscanned" : 4,
   "nscannedObjectsAllPlans" : 4,
   "nscannedAllPlans" : 4,
   "scanAndOrder" : false,
   "indexOnly" : false,
   "nYields" : 0,
   "nChunkSkips" : 0,
   "millis" : 0,
   "server" : "myMongoDB.local:27017",
   "filterSet" : false
}

• cursor displays BasicCursor to indicate a collection scan.
• n displays 1 to indicate that the query matches and returns one document.
• However, nscanned and nscannedObjects display 4 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:

copy

db.survey.ensureIndex( { item: 1 } )

To view how MongoDB uses the index for the following query on the item field, use explain():

copy

db.survey.find( { item: "abc" } ).explain()

explain() returns the following output:

copy

{
   "cursor" : "BtreeCursor item_1",
   "isMultiKey" : false,
   "n" : 1,
   "nscannedObjects" : 1,
   "nscanned" : 1,
   "nscannedObjectsAllPlans" : 1,
   "nscannedAllPlans" : 1,
   "scanAndOrder" : false,
   "indexOnly" : false,
   "nYields" : 0,
   "nChunkSkips" : 0,
   "millis" : 0,
   "indexBounds" : { "item" : [ [ "abc", "abc" ] ] },
   "server" : "myMongoDB.local:27017",
   "filterSet" : false
}

• cursor displays BtreeCursor item_1 to indicate index use and the name of the index.
• n displays 1 to indicate that the query matches and returns one document.
• nscanned displays 1 to indicate that MongoDB scanned one index entry.
• nscannedObjects displays 1 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:

copy

db.survey.find( { item: "abc" }, { item: 1, _id: 0 } )

To view how MongoDB uses the index for the query, use explain():

copy

db.survey.find( { item: "abc" }, { item: 1, _id: 0 } ).explain()

explain() returns the following output:

copy

{
   "cursor" : "BtreeCursor item_1",
   "isMultiKey" : false,
   "n" : 1,
   "nscannedObjects" : 0,
   "nscanned" : 1,
   "nscannedObjectsAllPlans" : 0,
   "nscannedAllPlans" : 1,
   "scanAndOrder" : false,
   "indexOnly" : true,
   "nYields" : 0,
   "nChunkSkips" : 0,
   "millis" : 0,
   "indexBounds" : { "item" : [ [ "abc", "abc" ] ] },
   "server" : "myMongoDB.local:27017",
   "filterSet" : false
}

• cursor displays BtreeCursor item_1 to indicate index use and the name of the index.
• n displays 1 to indicate that one document matches the query.
• nscanned displays 1 to indicate that MongoDB scanned one index entry.
• However, nscannedObjects displays 0 to indicate that MongoDB did not need to scan any document. MongoDB used just the index, as verified by indexOnly.
• indexOnly displays true 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:

copy

db.survey.ensureIndex( { ratings: 1 } )

And consider the following query:

copy

db.survey.find( { ratings: { $gte: 9 } } )

The query matches a single document:

copy

{ "_id" : 1, "item" : "xyz", "ratings" : [ 8, 9, 10 ] }

To view how MongoDB uses the index for the query, use explain():

copy

db.survey.find( { ratings: { $gte: 9 } } ).explain()

explain() returns the following output:

copy

{
   "cursor" : "BtreeCursor ratings_1",
   "isMultiKey" : true,
   "n" : 1,
   "nscannedObjects" : 1,
   "nscanned" : 2,
   "nscannedObjectsAllPlans" : 1,
   "nscannedAllPlans" : 2,
   "scanAndOrder" : false,
   "indexOnly" : false,
   "nYields" : 0,
   "nChunkSkips" : 0,
   "millis" : 0,
   "ratings" : [ [ 9, Infinity ] ]
   "server" : "myMongoDB.local:27017",
   "filterSet" : false
}

• cursor displays BtreeCursor ratings_1 to indicate index use and the name of the index.
• isMultiKey displays true to indicate that the index is multi-key.
• n displays 1 to indicate that one document matches the query.
• nscanned displays 2 to indicate that MongoDB scanned two index entries.
• However, nscannedObjects displays 1 since both keys refer to the same document and MongoDB de-duplicates.

See also

Analyze Query Performance, Query Optimization, Index Introduction, Query Plans

←   cursor.count() cursor.forEach()  →

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