When to Use explain() in MongoDB

In MongoDB, the command db.collection.explain() is an important operation to help you understand exactly how your queries execute and behave. Once you have a grasp of how MongoDB handles explain(), using it lets you examine queries in a number of ways. With explain(), you can:

• Inspect poorly performing queries and discover any bad or erroneous operations the query is employing. If you find yourself saying, "I have a slow query—what should I do now?" explain() is a good place to start.
• Find out how MongoDB intends to execute a query, what access type the query will use at each stage (such as an index scan or a collection scan), and what indexes are available to the query.
• Track and measure improvements or changes in query execution between one explain output and another.

In other words, understanding explain() gives you direct insight into how your MongoDB queries behave, and it provides a basis for measuring that behavior and its changes over time. 

Running explain() in MongoDB 

When you run explain() in MongoDB, some knowledge is required to understand the actual results that the function returns.  A core concept of MongoDB queries is the "winning plan."  Whenever the query optimizer runs queries, it selects its execution plan from an array of available indexes—different plans. When multiple possible plans are possible, the server determines the winning plan by running all of them in parallel for a short time; whichever plan demonstrates the best behavior during this test period is then selected to complete the query. This selected plan is the "winning plan." Once a winning plan is determined, it's then cached in the server until the collection changes in a meaningful way (by either data or index modification). Until then the cached plan becomes the default plan for the query every time it runs. When you use explain() to inspect a query, the returned results are reflections of the query's winning plan, broken down as a "tree" of stages. As MongoDB's documentation explains,

"Each stage passes its results (i.e. documents or index keys) to the parent node. The leaf nodes access the collection or the indices. The internal nodes manipulate the documents or the index keys that result from the child nodes. The root node is the final stage from which MongoDB derives the result set."

And finally, the stages themselves represent specific query actions. Or, in summary, when you run explain(), you're given a decision tree of query plan "stages," each branch of which represents the operation the query performs in that stage of the plan. Possible stage types include the following:

• COLLSCAN for a collection scan.
• IXSCAN for scanning index keys.
• FETCH for retrieving documents.
• SHARD_MERGE for merging results from shards.
• SORT for sorting results manually (as opposed to relying on an index for sorted output).

Below is an example of explain() output. This shows a collection of counters stored as documents with {x,y} coordinates. Note "winningPlan" and the resulting stages, each indicating a query operation, executed in the shown order.

db.counters.find({ “y”: { “$lt”: 10 } }).explain()

{

"queryPlanner" : {

"plannerVersion" : 1,

"namespace" : "vividcortex.counters",

"indexFilterSet" : false,

"parsedQuery" : {

"y" : {

"$lt" : 10

}

},

"winningPlan" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"keyPattern" : {

"y" : 1

},

"indexName" : "y_1",

"isMultiKey" : false,

"multiKeyPaths" : {

"y" : []

},

"isUnique" : false,

"isSparse" : false,

"isPartial" : false,

"indexVersion" : 2,

"direction" : "forward",

"indexBounds" : {

"y" : [

"[-inf.0, 10.0)"

]

}

}

},

},

"serverInfo" : {

"host" : "ip-172-31-70-222",

"port" : 27017,

"version" : "3.4.4",

"gitVersion" : "888390515874a9debd1b6c5d36559ca86b44babd"

},

"ok" : 1

}

 

Using explain() for Basic Diagnosis

Once you understand the process of running explain(), and you know how to read its results, you can begin to consider how different results should inform your diagnosis or corrective actions for a query. Below are a few different examples of results you might see and how you might interpret them:

• winningPlan.stage = “COLLSCAN”
  • This is a collection scan, which is expensive (in a previous article, we explored the concept of MongoDB indexes and how to determine if yours are effective.)
• Covered Queries
  • The index was sufficient to resolve the query without having to visit the document itself.
  • A query has a covering index for it if you see an IXSCAN stage that is not a child of a FETCH stage.
• SORT - the query cannot rely on an index for sorted output
  • If you sort along a field covered by an index, MongoDB automatically uses that index.

If you see that your query's stages all look good, it doesn't necessarily mean your query is fully optimized — there's still the possibility that your collection is using an inefficient index, which may require your attention.

Customize Your Results With explain(verbosity)

If you're interested in expanding or shrinking the size of your explain results, you have some control over that too. By adjusting explain(verbosity), you can change your results' verbosity—the amount of detail and explanation the results include. The default verbosity is "queryPlanner," which runs the optimizer to choose the winning plan. "executionStats" works the same way but also runs the plan and returns the query's execution stats, such as docs returned, docs examined, and index keys examined, all broken down by stage. Finally, "allPlansExecution" offers the same return as "executionStats" but with stats on all "losing" plans in addition to the winning plan. The example we included earlier in this article uses the default "queryPlanner" verbosity.

explain() Explained

There are several layers that go into reading MongoDB explain results and understanding them in a way that leads to optimal queries. We hope this post has given you a solid foundation for getting started with explain(), and, for our part, we hope to take a deeper look at some of the other aspects of explain() soon.