Reading a MongoDB explain() in five minutes
Rudra Narayan Panda ·
A slow query usually shows its cause in three numbers. Run the query with explain("executionStats") and read these before anything else.
the three numbers
- Stage names in
winningPlan.COLLSCANmeans a full collection scan.IXSCANmeans an index was used. ASORTstage means the database sorted the results itself instead of reading them in index order. totalKeysExaminedandtotalDocsExaminedagainstnReturned. Examining far more than you return is wasted work.- Where
FETCHsits. It loads full documents, so you want it to see as few as possible.
the experiment
A toy payments collection with 300,000 generated documents on MongoDB 8.2.5, run locally. About half the payments are captured, and amount is spread evenly from 1 to 1000. The query asks for the 20 newest captured payments over 900:
db.payments
.find({ status: "captured", amount: { $gt: 900 } })
.sort({ createdAt: -1 })
.limit(20)
.explain("executionStats")
Here is what I got for three setups:
| setup | plan | keys | docs |
|---|---|---|---|
| no index | SORT <- COLLSCAN | 0 | 300,000 |
index on status, amount, createdAt | FETCH <- SORT <- IXSCAN | 14,905 | 20 |
index on status, createdAt, amount | LIMIT <- FETCH <- IXSCAN | 265 | 20 |
All three return the same 20 documents. They differ in how much work it takes to find them.
why the second index is still slow
The second index filters well but cannot hand back results in createdAt order, because amount sits between status and createdAt. The database reads every matching key (14,905 of them), sorts them, then fetches the top 20. That SORT stage is the cost.
the rule behind the third index
Order compound index fields as Equality, Sort, Range. Equality fields first (status), then the sort field (createdAt), then range fields (amount). Now the database walks the index already in sort order, checks amount from the index itself, and stops after 20 matches. It examined 265 keys, close to the 20 / 0.1 you would expect when about one in ten captured payments passes the range.
db.payments.createIndex({ status: 1, createdAt: -1, amount: 1 })
Timing on this small local run was 68 ms, then 7 ms, then 1 ms. I would not quote that. Keys and documents examined are the stable signal, and timing on a tiny dataset is noisy.
one caveat
Equality, Sort, Range is a starting point, not a law. If the range is very selective and the sort has no limit, putting the range before the sort can examine fewer keys. Run explain on your own query and compare, as above.
the surprise
Once I could read a query plan, I couldn't stop seeing them. Queries I'd written off as fine, mine and the team's alike, showed a COLLSCAN or a SORT stage I had never looked for, and fixing them took up the next couple of weeks. The surprise wasn't the rule. It was how much I'd been missing by never running explain.
what to check next
- Run the same
explainon your slowest real query and look forCOLLSCANand aSORTstage. - Compare
totalKeysExaminedandtotalDocsExaminedwithnReturned. - Remember each index costs write time and memory, so add one deliberately and drop the ones you replace.