HowToCodePage

Performance tuning with explain() and the profiler

Read a query plan instead of guessing, find the slow operations with the profiler, and know which changes actually reduce the time a query takes.

explain() verbosity and reading a plan

db.orders.find({
  status: "paid",
  placedAt: { $gte: ISODate("2026-01-01") }
}).explain("executionStats");

db.orders.aggregate([
  { $match: { status: "paid" } },
  { $group: { _id: "$customer.id", n: { $sum: 1 } } }
]).explain("queryPlanner");
FieldWhat to look for
winningPlan.stageIXSCAN is an index seek; COLLSCAN is a full scan
totalKeysExaminedIndex keys read; a large number with few results means a loose bound
totalDocsExaminedDocuments fetched; should be close to nReturned
nReturnedDocuments actually delivered
executionTimeMillisTotal time, including any blocking sort
usedDisk and spillsAn in-memory stage exceeded its budget and went to disk
rejectedPlansWhat the planner considered and discarded

The three verbs are cumulative: queryPlanner shows the chosen plan, executionStats runs it and reports counts, and allPlansExecution also reports the plans that lost. Use the last one when you cannot see why a worse plan won.

The profiler and index statistics

db.setProfilingLevel(1, { slowms: 100 });      // 0 off, 1 slow, 2 everything

db.system.profile.find({ millis: { $gt: 100 } })
                 .sort({ ts: -1 }).limit(5);

db.system.profile.find({ planSummary: "COLLSCAN" }).count();

db.orders.aggregate([{ $indexStats: {} }]);     // uses per index since restart
db.orders.stats().indexSizes;
  • Level 2 profiles every operation and is for short investigations only; on a busy server it becomes a performance problem in its own right.
  • planSummary records the shape of the plan without a re-run, which is often enough to spot the collections that are being scanned.
  • $indexStats reports accesses per index since the last restart. An index with zero accesses costs writes and nothing else.
  • Profiler entries contain the full command, including any literal values in the filter. Treat that collection as sensitive data with its own retention.

The changes that actually help

  • Return fewer documents. A query that pulls half a million rows to count them is an aggregation job, not a find.
  • Cover the query: an index containing both the filter and the projected fields lets the engine answer without fetching documents.
  • Order the index by equality, then sort, then range. A range field placed before a sort field forces an in-memory sort.
  • Bound the result with limit. A sort without a limit sorts the whole candidate set.
  • Look for $regex without a leading anchor, and for $ne and $nin, none of which can seek an index.
  • Schema changes count as performance work. A bucket pattern can turn a million documents into ten thousand.
⚠️
An index is not always the answer. If totalDocsExamined already matches nReturned, the query is doing minimal database work and the remaining time is network, sorting or the client. Measure the whole path before adding another index.

FAQ

Why does the planner choose the slower plan?
It estimates from statistics and from the constants in the query. A skewed value distribution, a plan cache entry from an unrepresentative parameter, or a predicate the planner cannot evaluate such as an unanchored regex all cause a bad estimate. Check allPlansExecution to see what it compared.
How do I find which queries are slow?
Enable slow-query profiling at a threshold you care about, then group db.system.profile entries by planSummary and by the shape of the filter. That separates many cheap operations from a few genuinely expensive ones, which need different fixes.

Indexes and schema design patterns The aggregation pipeline

Last refreshed 2026-09-18.