Why Guess When You Can Explain?
When a MongoDB query starts dragging down your API performance, the temptation is to immediately throw more indexes at the collection. But blindly indexing without understanding the underlying database execution path can lead to index bloat, wasted memory (RAM), and degraded write performance.
To truly understand why a query is slow, you must bypass query logs and run .explain("executionStats"). This returns the structural plan chosen by the query optimizer and tells you exactly how much work the database engine performed to return your documents.
Comparing COLLSCAN vs IXSCAN
The first thing to inspect in any explain plan is the winning plan stage. This tells you whether the database engine performed a full collection scan (COLLSCAN) or utilized an index (IXSCAN).
Let's look at a side-by-side comparison of execution stats in an unindexed versus indexed query using the visualization tool:
The database engine is forced to load and parse every single document into memory sequentially because no matching index exists for the query predicate. This causes high CPU usage and thread locks.
{
"queryPlanner": {
"winningPlan": {
"stage": `"COLLSCAN"`
}
},
"executionStats": {
"nReturned": 12,
"totalKeysExamined": 0,
"totalDocsExamined": 152045,
"executionTimeMillis": 482
}
}As shown in the visualizer:
- COLLSCAN (Collection Scan): The database is forced to scan every single document in the collection. The
totalDocsExaminedwill match the total collection count, leading to high latency spikes and CPU lockups. - IXSCAN (Index Scan): The engine searches the index's B-Tree keys directly, matching the query predicate immediately. Only the matching documents are fetched.
The Metric That Matters: Scan-to-Return Ratio
Beyond looking at the stage, you must check the efficiency ratio of your query execution:
A perfect query has a 1:1 ratio (meaning for every document examined, one document was returned). If your explain plan shows a ratio higher than 100:1 (e.g., examining 150,000 documents to return 12), your query is highly inefficient.
By identifying these ratios automatically, TraceMole highlights which files in your code repositories are initiating unindexed database queries, saving you hours of manual debugging.