← writing

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

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:

setupplankeysdocs
no indexSORT <- COLLSCAN0300,000
index on status, amount, createdAtFETCH <- SORT <- IXSCAN14,90520
index on status, createdAt, amountLIMIT <- FETCH <- IXSCAN26520

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