Query Performance
explain()
Ask the planner what it did. The first tool when a query is slow.
A slow query is a measurement problem first. `explain()` shows the plan and, with executionStats, how many keys and documents the server actually touched. Do not add an index until you have seen this output. 'I would add an index' without numbers is the junior answer.
db.orders.find({ status: "delivered" }).explain("executionStats")winningPlan.stage: COLLSCAN nReturned: 40 totalKeysExamined: 0 totalDocsExamined: 800 executionTimeMillis: 12
| Field | Meaning |
|---|---|
| nReturned | Documents you got back |
| totalDocsExamined | Documents the server opened |
| totalKeysExamined | Index keys walked (0 on a collection scan) |
| executionTimeMillis | Wall time for this run — noisy, still useful as before/after |
`queryPlanner` — cheapest: winning plan, no execution counts. `executionStats` — the default for a slow query. `allPlansExecution` — how the trial of competing plans went; later lesson. In Node: collection.find(filter).explain('executionStats'). The ratio that matters is examined / returned. Close to 1 is healthy. 800 examined for 40 returned is a scan or a useless index.
Interview question
A query is slow. How do you diagnose it?
explain('executionStats'). Compare totalDocsExamined and totalKeysExamined to nReturned. A large gap means scanning. COLLSCAN where I expected IXSCAN means no usable index. I also look for a SORT stage — an in-memory sort an index could have provided. The ratio of examined to returned is the first sentence, not 'add an index'.
Practice
These are the filters you would explain first.