MongoDB’s explain command can feel like a black box, but it’s actually one of the most powerful tools for understanding and optimizing your database performance. The most surprising thing about explain is that it doesn’t actually run your query; it just tells you how it would run it.
Let’s see it in action. Imagine you have a collection users with documents like:
{
"_id": ObjectId("..."),
"username": "alice",
"email": "alice@example.com",
"signup_date": ISODate("2023-01-15T10:00:00Z"),
"profile": {
"city": "New York",
"country": "USA"
}
}
And you’re running a query like db.users.find({ "profile.city": "London" }). To understand how MongoDB would execute this, you’d use explain():
db.users.find({ "profile.city": "London" }).explain()
This command returns a JSON document detailing the query plan. The most critical part of this output is the executionStats (if you use explain("executionStats") or explain("allPlansExecution")), which shows how the query actually performed when it ran.
Here’s a simplified executionStats output:
{
"queryPlanner": {
// ... details about the chosen plan ...
},
"executionStats": {
"executionSuccess": true,
"nReturned": 15,
"executionTimeMillis": 550,
"totalKeysExamined": 10000,
"totalDocsExamined": 10000,
"executionStages": {
"stage": "COLLSCAN",
"direction": "forward",
"docsExamined": 10000,
"keysExamined": 0,
"numChildren": 0
}
},
"serverInfo": { ... },
"ok": 1
}
The executionStats tell us:
nReturned: How many documents matched the query (15 in this case).executionTimeMillis: How long the query took (550 milliseconds).totalKeysExamined: The number of index keys scanned.totalDocsExamined: The number of documents scanned.executionStages: This is the heart of it. It describes the operations MongoDB performs. In this example,COLLSCANmeans a collection scan.
The problem here is COLLSCAN. MongoDB had to read every document in the users collection (10,000 of them) to find the 15 matching the city "London". This is inefficient, especially as your collection grows.
To fix this, we need an index. The explain output helps us identify which fields to index. Since we’re querying on profile.city, an index on that field is the logical choice.
First, let’s create the index:
db.users.createIndex({ "profile.city": 1 })
Now, if you run the same explain("executionStats") command again:
db.users.find({ "profile.city": "London" }).explain("executionStats")
You’ll see a very different executionStats output:
{
"queryPlanner": {
// ... details about the chosen plan ...
},
"executionStats": {
"executionSuccess": true,
"nReturned": 15,
"executionTimeMillis": 5, // Much faster!
"totalKeysExamined": 15, // Only examined 15 keys
"totalDocsExamined": 15, // Only examined 15 documents
"executionStages": {
"stage": "IXSCAN",
"keyPattern": { "profile.city": 1 },
"indexName": "profile.city_1",
"direction": "forward",
"keysExamined": 15,
"docsExamined": 15,
"numChildren": 0
}
},
"serverInfo": { ... },
"ok": 1
}
Notice the dramatic improvement: executionTimeMillis dropped from 550ms to 5ms. totalKeysExamined and totalDocsExamined are now 15, matching nReturned. The executionStages show IXSCAN, indicating an index scan, which is exactly what we want.
The explain command has several modes:
explain("queryPlanner"): Shows the winning plan and why it was chosen, without executing the query.explain("executionStats"): Executes the query and shows statistics about its performance. This is what we used above.explain("allPlansExecution"): Executes the query and shows statistics for all candidate plans, not just the winning one. This is useful for understanding why a particular plan was chosen over others.
You can also use explain with update, remove, and aggregate operations. For example, to explain an update:
db.users.update({ "profile.city": "London" }, { $set: { "status": "active" } }).explain("executionStats")
Understanding the executionStages is crucial. You’ll see stages like:
COLLSCAN: Scans the entire collection. Bad for performance.IXSCAN: Scans an index. Good.FETCH: Retrieves the document after an index scan. Necessary but adds overhead.SORT: Sorts results. Can be expensive if not covered by an index.LIMIT: Limits the number of documents returned.PROJECTION: Reshapes the output document.
The winningPlan within the queryPlanner section shows the plan MongoDB chose. The rejectedPlans array shows plans that were considered but discarded. Examining these can reveal why MongoDB made a particular choice.
A common pitfall is assuming that just because an index exists, it will be used. MongoDB’s query optimizer has heuristics. If a query is expected to scan a large portion of an index (e.g., db.users.find({}, { "profile.city": 1 }) which might scan most of the index), or if the number of documents scanned via index is similar to a collection scan, the optimizer might opt for a COLLSCAN. This is where explain("allPlansExecution") is invaluable.
The indexBounds field in IXSCAN stages is also telling. It shows the range of index entries that were considered. For instance, [ MinKey, MaxKey ] indicates the entire index was scanned. [ "London", "London" ] indicates a very targeted scan.
The most counterintuitive aspect of query optimization is that sometimes MongoDB might choose a COLLSCAN over an IXSCAN if it estimates that scanning a large portion of the collection is faster than traversing a large index and then fetching documents. This often happens with very small collections or when the query is highly selective but the index is very wide and fetching documents is cheap.
The next step after optimizing individual queries is understanding compound indexes and the order of fields within them, which significantly impacts which queries can utilize them effectively.