MongoDB / Advanced: distributed and recoverable operations / Lesson 13

Explain plans and workload performance

Investigate query work, selectivity and contention without treating an index name or one timing sample as proof. Read MongoDB explain results in their version context.

About 4 min readContent reviewed 2026-10-25

On this page

Lesson goals

Interpret planner and execution-statistics verbosity separately.
Choose index, query or capacity changes from evidence.

Measure the actual service question

Capture the query shape, parameter classes, projection, sort, expected documents and user-visible latency target. Customer distributions can be skewed, so one small tenant is not a representative benchmark for a large tenant. Compare repeated latency and throughput under approved isolated concurrency, with cache state and data size recorded. A query may wait on storage or compete for resources even when it examines few documents. Never make it appear faster by removing tenant scope, required history or a business correctness predicate.

Ask which access path the planner considered

queryPlanner verbosity reports plan information without providing a complete measured execution summary. Planning itself can involve candidate-plan trials, so it is not a promise of absolutely zero work. The query below asks for one customer’s recent timeline and needs an explicitly prepared synthetic fixture. Inspect winning plan, index bounds, filters and sort stages together. Explain output structure varies by engine and release, including differences between classic and slot-based execution; do not hardcode one nested JSON path as universal monitoring truth.

javascript Illustrative mongosh; results are reasoned, not measured.

db.getSiblingDB('quay_lab').shipments.find(
  { customerId: 7 }, { _id: 1, createdAt: 1 }
).sort({ createdAt: -1, _id: -1 }).limit(20).explain('queryPlanner');

No plan or timing cells are invented. Index eligibility and the chosen strategy depend on the real fixture.

Read examined work beside returned results

executionStats runs the winning read plan and reports metrics such as nReturned, totalKeysExamined and totalDocsExamined. Compare them in context: a legitimate aggregate can examine many source documents for one summary, while a point lookup examining thousands may indicate a problem. A covered query can avoid fetching documents under documented conditions, but multikey, projection and filtering details matter. allPlansExecution includes candidate-trial information, not a full independent benchmark of every possible plan. Label estimates, trials and executed results distinctly in reports.

Execute analysis only on a bounded approved fixture

This pipeline analysis really performs the read work for its execution statistics. It is non-runnable here and must not be copied into a production load experiment. Begin with a representative isolated collection and verify that the pipeline has no $out or $merge. Compare stage cardinalities before grouping and sorting, not only the final count. A small output can conceal a large scan. Explain does not use the ordinary plan cache in the same way as a normal request and does not establish the exact steady-state application latency.

javascript Illustrative mongosh; results are reasoned, not measured.

db.getSiblingDB('quay_lab').shipments.explain('executionStats').aggregate([
  { $match: { state: 'pending', customerId: 7 } },
  { $group: { _id: '$destinationId', count: { $sum: 1 } } },
  { $sort: { count: -1, _id: 1 } }
]);

Expected output is execution analysis, with environment-dependent counts and timing. None were measured for this course.

Change the evidenced bottleneck

If examined work is excessive, test a matching compound or partial index, a semantics-preserving pipeline rewrite or a maintained summary. If work is reasonable but peak latency grows, investigate connection queueing, hot documents, storage, cache and topology. Profiling and logs can contain sensitive values and have overhead, so enabling them belongs to an authorized procedure. Avoid universal cache or memory prescriptions. Accept a change only after representative parameters, correctness, write cost, storage, latency distribution and rollback criteria are evaluated together.

Common errors and fixes

An IXSCAN label is reported as proof of efficiency

Why it happens: Examined keys, fetched documents and residual filtering were ignored.
How to fix it: Compare examined work with the query’s intended result and distribution.
Prevent it next time: Use metrics and representative parameters, not stage labels alone.

Explain timing is treated as application latency

Why it happens: Instrumentation, cache behavior and request overhead were conflated.
How to fix it: Measure representative application paths separately in an approved environment.
Prevent it next time: Record exactly which command produced every timing.

A faster rewrite loses business documents

Why it happens: Filters, array semantics or tenant scope changed.
How to fix it: Compare identifiers and aggregates before accepting performance.
Prevent it next time: Make semantic equivalence an explicit tuning gate.

Check your understanding

1. A point lookup returns one document after examining 50,000. What should be checked before adding another index?

Show worked solution

Inspect the actual filter types, index bounds, residual predicates, collation and parameter distribution. Confirm that the intended index exists and matches the query shape, then compare a justified candidate on realistic data. Another index cannot repair a wrong type or missing tenant predicate, and a forced hint based on one sample may harm other parameter classes.

2. An aggregation returns one total after scanning a million matching payments. Is the ratio alone proof of failure?