Explain

Basics

A great way to get more information on the performance of your database queries is to use the $explain feature. This will display "explain plan" information about a query from the database.

When using the mongo shell, invoke the explain() method on a cursor. The result will be a document that contains the explain output. Note that explain runs the actual query to determine the result. If the query is slow, the explain will be slow too.

Some of the options below may not appear depending on the version of mongodb you are running.

> db.collection.find(query).explain()

provides information such as the following:

{
    "cursor" : "BasicCursor",
    "indexBounds" : [ ],
    "nscanned" : 57594,
    "nscannedObjects" : 57594,
    "nYields" : 2 ,
    "n" : 3 ,
    "millis" : 108,
    "indexOnly" : false,
    "isMultiKey" : false,
    "nChunkSkips" : 0
}

Output Fields

cursor

The type of cursor used for scanning.

• BasicCursor - indicates a table scan style operation
• BtreeCursor - an index was used. When an index is used, indexBounds will be set to indicate the key bounds for scanning in the index.

nscanned

Number of items (documents or index entries) examined. Items might be objects or index keys. If a "covered index" is involved, nscanned may be higher than nscannedObjects.

nscannedObjects

Number of documents scanned.

nYields

Number of times this query yielded the read lock to let waiting writes execute.

n

Number of documents matched (on all criteria specified).

millis

Time for the query to run, in milliseconds.

scanAndOrder

True if the index could not be used for sorting.

indexOnly

True if the results of the query can be returned using only the index. See SERVER-5759 for more information on the behavior of this value.

isMultiKey

If true, a multikey index was used.

With Sharding

In a sharded deployment, explain outputs some additional fields. An example:

	"clusteredType" : "ParallelSort",
	"shards" : {
		"shard1:27017" : [
			{
				"cursor" : "BtreeCursor a_1",
				"nscanned" : 1,
				"nscannedObjects" : 1,
				"n" : 1,
				"millis" : 0,
				"nYields" : 0,
				"nChunkSkips" : 0,
				"isMultiKey" : false,
				"indexOnly" : false,
				"indexBounds" : {
					"a" : [
						[
							1,
							1
						]
					]
				}
			}
		]
	},
	"n" : 1,
	"nChunkSkips" : 0,
	"nYields" : 0,
	"nscanned" : 1,
	"nscannedObjects" : 1,
	"millisTotal" : 0,
	"millisAvg" : 0,
	"numQueries" : 1,
	"numShards" : 1

clusteredType

• ParallelSort means all shards are accessed in parallel
• SerialServer means shards are queried one by one in order

shards

List of all the shards accessed during the query and the explain output for each shard.

nChunkSkips

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.

millisTotal

Total time for the query to run, in milliseconds.

millisAvg

Average time for the query to run on each shard, in milliseconds.

numQueries

Total number of queries executed.

numShards

Total number of shards queried.

See Also

• Optimization
• Database Profiler
• Viewing and Terminating Current Operation