Walk me through the output of .explain('executionStats'). What are the red flags for a senior dev?
A senior developer scans explain('executionStats') for a systematic list of red flags: high totalDocsExamined vs. nReturned, full collection scans (COLLSCAN), inefficient index use, and in sharded clusters, unintentional scatter-gather operations.
When you append .explain("executionStats") to your query, MongoDB doesn't just plan the query; it actually executes the winning plan selected by the query optimizer and returns detailed statistics about that execution . This means the output reflects real resource consumption, not just a theoretical plan. Understanding this output is key to moving beyond guessing and into data-driven query optimization .
A senior developer scans this output not just for correctness, but for efficiency and scalability. The presence of an index doesn't mean it's the right index, and a low execution time on a small dataset can hide massive problems waiting to appear at scale. Let's walk through the key sections and the specific red flags we look for.
The first section a senior dev looks at is executionStats. It provides the high-level metrics that immediately signal the query's health .
The ideal state, indicating a highly efficient, well-indexed query, is nReturned == totalKeysExamined == totalDocsExamined . This means the query used the index to pinpoint exactly the right documents and fetched only those.
A senior dev's alarm bells ring with the following patterns:
After the high-level stats, a senior dev drills into the executionStages tree. This shows the step-by-step process of how the query was executed. The stage name is the most critical piece of information here .
On a sharded cluster, .explain() output becomes even more critical. A senior dev will pay close attention to the shards array, which contains the executionStats for each involved shard .
Ultimately, walking through an .explain() is about answering a core set of questions. If the answer to any of these is 'no' or 'unsure', further investigation is needed.
Scenario Questions
- 1
You run db.users.find({age: {$gt: 30}}).explain('executionStats') and see totalDocsExamined 5000 but nReturned 10. What does that tell you about the query?
- 2
Which part of the executionStats output would you look at to confirm whether an index is being used?
- 3
If the executionTimeMillis field shows 150 ms for a simple find, what might be a first step to investigate?
- 1
Your new feature adds a filter on a nested field and the explain output now shows a COLLSCAN. How would you debug and fix it?
- 2
After a recent schema change, executionTimeMillis doubled for a frequent query. Walk me through how you'd use executionStats to locate the regression.
- 3
You notice a large sort stage with 'memory usage: 120 MB' in the output. What concerns does this raise and what options do you have?
- 1
Our service sees intermittent latency spikes; executionStats reveals high totalKeysExamined on a sharded cluster. How would you redesign the query or data model to reduce that cost?
- 2
A teammate's aggregation pipeline shows multiple FETCH and UNWIND stages and a total execution time of 2 seconds. Which red flags would you prioritize and why?
- 3
Explain how you would use executionStats to evaluate the impact of adding a compound index versus rewriting the query logic.
- 1
We are migrating from a single‑region MongoDB deployment to a globally sharded cluster. How would you leverage executionStats across services to define an index strategy that avoids performance regressions?
- 2
Across several microservices, many queries exhibit similar red flags in their executionStats output. Describe a systematic, organization‑wide approach to monitor, refactor, and enforce best practices.
- 3
When planning a major data model overhaul, how would you use baseline executionStats to set performance targets and validate that the new design meets them at scale?
Follow-up Questions
- ❓What would you change in the query or schema to eliminate a COLLSCAN?
- ❓How do you prioritize which red flag to address first?
- ❓Can you describe a metric you would monitor to catch these issues proactively?