b¬äd†ødd

MongoDB: Explain Plan

April 30, 2014 by Brad

Overview:

MongoDB provides a query explain capability that can be used to diagnose performance issues, or simply provide insight into the metrics relative to the query.

In creating a simple 1,000,000 document collection via

> for ( i = 0; i < 1000000; i++ ) {
    db.users.insert( {
        "i" : i,
    	"username" : "user" + i,
    	"age" : Math.floor(Math.random() * 100),
    	"created" : new Date()
    } );
}

and, executing the following query's explain()

> db.users.find( { username : "user101" } ).explain()

produces the result of

{
  "cursor" : "BasicCursor",
  "isMultiKey" : false,
  "n" : 1,
  "nscannedObjects" : 1000000,
  "nscanned" : 1000000,
  "nscannedObjectsAllPlans" : 1000000,
  "scanAndOrder" : false,
  "indexOnly" : false,
  "nYields" : 7812,
  "nChunkSkips" : 0,
  "millis" : 627,
  "server" : "127.0.0.1:27017",
  "filterSet" : false
}

From this we can deduce

• no index was used ← "cursor" : "BasicCursor"
• one document was actually returned ← "n" : 1
• the query execution time was 0.627 seconds ← "millis" : 627
• 1,000,000 documents were scanned ← "nscanned" : 1000000

Adding an index to the collection by the way of

> db.users.ensureIndex( { "username" : 1 } )

coupled with the original query

> db.users.find( { username : "user101" } ).explain()

now produces an explain() result of

{
  "cursor" : "BtreeCursor username_1",
  "isMultiKey" : false,
  "n" : 1,
  "nscannedObjects" : 1,
  "nscanned" : 1,
  "nscannedObjectsAllPlans" : 1,
  "scanAndOrder" : false,
  "indexOnly" : false,
  "nYields" : 0,
  "nChunkSkips" : 0,
  "millis" : 0,
  "indexbounds" : {
	"username" : [
  	[
    	"user101",
    	"user101"
  	]
	]
  },
  "server" : "127.0.0.1:27017",
  "filterSet" : false
}

Now we see

• an index is used ← "cursor" : "BtreeCursor username_1"
• the index does not contain an array ← "isMultiKey" : false
• one document was actually returned ← "n" : 1
• one index pointer was followed to a document ← "nscannedObjects" : 1
• one index entry was looked at ← "nscanned" : 1
• a total of one scanned object (in case of multiple objects; e.g. OR clause) ← "nscannedObjectsAllPlans" : 1
• no sorting was performed in memory ← "scanAndOrder" : false
• the query was not solved with a covered index ← "indexOnly" : false
• the query was not paused for a write request ← "nYields" : 0
• the query execution time was less than 1 milli-second ← "millis" : 0
• the bounds of the index used: user101 ← "indexbounds" : { "username"[ [ "user101", "user101" ] ] }

Some of these attributes take on slightly different meanings depending on the query's use of an index or not.

• nscannedObjects → with an index it is the number of times it follows a pointer; otherwise it is most likely a full collection scan.
• nscanned → the number of index entries looked at or number of documents scanned if an index was not used.

I hope this helps shed a little light on the MongoDB explain() capability.

Cheers
-Brad