MongoDB / Editorial solution guide

Diagnose slow queries, indexes and aggregation plans

Separate inefficient examined work from distributed routing and resource waits. Use MongoDB explain as evidence while preserving document and array semantics.

About 3 min readContent reviewed

Read-only knowledge base ยท No public comments

Recognize the symptoms

A timeline query slows as data grows, a new index is ignored, or an aggregation returns a small report after long work. A COLLSCAN or IXSCAN label alone does not prove failure or success. Preserve query shape, actual value types, sort, projection, returned-document expectations and latency distribution before changing anything.

Understand the likely causes

  • Causes can include unselective filters, unsuitable compound prefixes, collation/type mismatch, multikey restrictions, a broad $lookup, repeated $unwind fan-out, stale application assumptions or cluster contention. Sharded fan-out can remain expensive despite efficient local indexes. A small final result does not imply little source work, especially for totals requiring many legitimate events.

Confirm correctness and representative inputs

Compare expected identifiers and aggregate totals on a controlled synthetic fixture, including missing fields, arrays and type variations. Record common and extreme tenant values rather than one convenient parameter. Distinguish cold and warm cache conditions and approved concurrency. Do not replay production traffic or run an ad hoc load experiment against a configured service. The first decision is whether the query performs too much work or waits for reasonable work; those problems need different remedies.

Read a bounded planner explanation

Inspect winning access path, index bounds, residual filters and sort requirements together. queryPlanner reports planning information and can involve trials, so do not call it absolutely work-free. Explain structure varies by MongoDB release and execution engine; inspect the returned shape instead of parsing one assumed JSON path forever. The pattern below uses a training collection and supplies no invented plan numbers or latency. Match actual BSON parameter types and query collation to the index contract.

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');

A candidate compound index must be judged against this complete query shape and the actual data distribution.

Compare examined and returned work carefully

On an approved isolated fixture, executionStats executes the winning read plan and reports nReturned, totalKeysExamined and totalDocsExamined. allPlansExecution adds candidate-trial data rather than a complete benchmark of every alternative. Explain bypasses ordinary plan-cache behavior and should not be equated with steady-state application latency. Inspect stage cardinality before and after $unwind, $lookup and $group. Confirm that a diagnostic pipeline contains no $out or $merge, because those stages write. Even a read-only execution can be expensive.

Repair the evidenced mismatch or access pattern

A type inventory can expose numeric customer identifiers mixed with strings before a new index is proposed. The query below observes types only. If the model is consistent but examined work is excessive, test a suitable compound/partial index or semantics-preserving rewrite on representative data. If waits dominate, investigate capacity, hot documents, replication or routing instead. Index builds, profiler enablement and cluster configuration changes belong to separate authorized procedures. Never remove tenant scope or substitute different array matching merely to improve a timing number.

javascript Illustrative mongosh; results are reasoned, not measured. 
db.getSiblingDB('quay_lab').shipments.aggregate([
  { $group: { _id: { $type: '$customerId' }, count: { $sum: 1 } } },
  { $sort: { _id: 1 } }
]);

Expected output is the actual type distribution of the prepared fixture, not an automatic conversion or repair.

Verify the fix

After a reviewed change, compare identical logical results, common and skewed parameters, examined work, latency percentiles and write/storage cost. Confirm both router-level and per-shard behavior when applicable. Retain a rollback criterion and identify the measured bottleneck resolved. One cached fast run or the appearance of an index name is insufficient acceptance evidence.

Keep it from recurring

  • Review indexes against real query shapes and growth, enforce consistent BSON types, and bound array and lookup cardinality. Track latency and examined-work trends without exposing sensitive literals. Recheck explain interpretation and driver compatibility after version upgrades rather than assuming diagnostic structure is permanent.

Official references