IXSCAN is not a performance verdict. A query can use an index and still examine excessive keys, fetch most of a collection, sort or spill, amplify an aggregation, and consume more total capacity than the single slowest operation.

Technology and product capabilities in this series are evaluated as of June 30, 2026.

Situation

MongoDB Part 1 classified an incident at the host, cache, connection, replication, topology, and workload layers. Suppose workload evidence remains: operation demand changed, query-executor work rose, and the affected member consumed more CPU or I/O. The next question is not “Which query was slow?” It is “Which normalized operation shape created the resource demand?”

Self-managed MongoDB exposes slow-operation evidence in diagnostic logs and the database profiler. explain() reveals candidate plans, index bounds, execution stages, and work performed by a representative operation. Historical evidence finds recurring shapes; explain() tests one representative value now.

An LLM can connect shapes, plan evidence, pipeline structure, schema facts, and incident timing. It cannot safely infer an index from query text alone or treat one laboratory execution as production proof.

The Problem

Slow-query lists overemphasize latency outliers. A moderate operation executed continuously may dominate total resource consumption. Sampling misses some executions, while one pathological literal can make an otherwise acceptable shape look universally bad.

MongoDB 8.0 also exposes multiple identifiers that must not be conflated. queryShapeHash identifies the newer query shape used by query settings. planCacheShapeHash identifies a plan-cache query shape. planCacheKey additionally depends on indexes available to that shape; even similarly shaped operations can have different keys when a partial index is eligible for one predicate value but not another.

Finally, explain() ignores existing plan-cache entries and prevents its winner from being cached. Output differs between classic and slot-based execution, and execution verbosity performs real work.

How do we move from sampled operations to a proven shape-level diagnosis without turning production into a benchmark environment?

Build a Shape-to-Proof Workflow

flowchart TD
    A[incident workload and resource interval] --> B[slow diagnostic logs and bounded profiler evidence]
    B --> C[normalize and group by namespace command and shape]
    C --> D[rank by frequency latency and examined work]
    D --> E[select representative and adverse values]
    E --> F[bounded explain and plan-cache evidence]
    F --> G[index pipeline schema and data-distribution analysis]
    G --> H[LLM observations hypotheses contradictions and gaps]
    H --> I[DBA validation on representative data]
    I --> J[reversible change and before-after proof]

Retain UTC interval, member and role, namespace, command, shape identifiers, duration, keys and documents examined, returned count, sort and disk-use indicators, plan summary, query framework, application identity, and sanitized representative structure. Preserve count, sum, percentiles, and maximum per shape; omit raw literals by default.

Rank shapes on more than maximum latency. Use execution frequency, cumulative latency, keys examined, documents examined, disk use, errors, and overlap with the incident resource curve. The LLM receives these deterministic aggregates and the evidence lineage behind them.

Symptoms

Evidence patternPlausible mechanismRequired proof
COLLSCAN with high documents examinedno eligible index or scan is cheaperindexes, predicate, cardinality, execution stages
IXSCAN with high keys examinedbroad bounds, low selectivity, multikey expansionbounds, key pattern, representative values
High documents examined after IXSCANresidual filtering or non-covering fetchFETCH filter, projection, index fields
Explicit SORT or hasSortStageindex does not provide required ordersort keys, prefix equalities, memory and disk use
Aggregation uses diskblocking stage exceeded its memory allowancepipeline stages, usedDisk, input cardinality
$lookup latency risesouter fan-out or inefficient foreign accessouter row count, foreign plan and index eligibility
Same shape has mixed latencyliteral selectivity, cache state, concurrency, or plan behaviorvalue buckets, plan keys, host and timeline evidence

First Five Checks

1. Discover shapes without profiling everything

Start with existing diagnostic slow-operation logs. Preserve the configured slow threshold, sampling rate, log component, member, and time window, because absence from sampled slow evidence is not proof of absence.

Use the database profiler only through an approved, time-bounded collection plan. It operates per database, writes to the capped system.profile collection, can affect performance and disk usage, and can expose unencrypted query data. It is not available through mongos; a sharded deployment requires member-aware collection. Restore the prior profiling configuration after capture.

Normalize literals before model access and retain types, operators, value buckets, sort, projection, collation, hint, read concern, and pipeline order when they distinguish behavior. Security and retention controls belong in Part 4.

2. Preserve shape and plan identity

Group historical evidence by namespace, command, and the identifier actually emitted by that source. In MongoDB 8.0, prefer planCacheShapeHash over its deprecated duplicate queryHash for plan-cache analysis. Keep queryShapeHash separate. Store planCacheKey, index catalog version, server version, and queryFramework beside each plan.

Do not merge on truncated query text or assume one literal represents the shape. Partial-index eligibility, collation, types, arrays, and data skew can change the plan or cost.

3. Run bounded explain at the right verbosity

Begin with queryPlanner to inspect the winning and rejected plan structure. Use executionStats only for representative operations under a time, load, and data-sensitivity budget. Reserve allPlansExecution for a focused planner question; it adds candidate-plan detail but is not routine telemetry.

db.orders.explain("executionStats").find(
  { tenantId: "<token>", status: "pending", createdAt: { $gte: ISODate("<bucket>") } },
  { _id: 0, status: 1, createdAt: 1, total: 1 }
).sort({ createdAt: -1 }).limit(50)

Prefer representative data in a controlled environment. On production, bound execution and avoid broad values. Compare explain() with historical plan summaries and $planCacheStats; do not claim it reproduced the cached incident path.

4. Read work, not just stage names

Walk the plan tree. Compare totalKeysExamined and totalDocsExamined with nReturned, but retain absolute counts and zero-result cases. Inspect index bounds, residual filters under FETCH, explicit SORT, limit placement, seeks, spills or usedDisk, and execution estimates available for the deployed version.

COLLSCAN can be rational for a tiny collection or an unselective predicate. IXSCAN can be poor when its bounds cover most keys. A covered query generally avoids document fetch and can report zero documents examined, but a wider covering index increases storage, cache demand, and write maintenance.

Do not require one fixed nesting path. MongoDB documents different classic and slot-based structures and warns that explain fields can change. Store raw versioned evidence plus normalized fields and parser version.

5. Expand aggregation plans into cardinality flow

For aggregations, inspect the optimized plan rather than judging source order alone. MongoDB can move eligible $match predicates ahead of projections or sorts. Early $match, compatible $sort, and certain $group patterns can use indexes; later stages that access another collection can use that collection’s indexes.

Track documents entering and leaving blocking or fan-out stages. $unwind can multiply rows; $group and $sort hold state; $lookup cost combines outer cardinality and foreign work. Correlated field-to-constant comparisons can use an eligible foreign index; field-to-field comparisons cannot.

Decision Tree

flowchart TD
    A[expensive MongoDB shape] --> B{historical evidence overlaps incident}
    B -->|no| C[lower priority and find correlated shape]
    B -->|yes| D{bounded explain reproduces excessive work}
    D -->|no| E[test representative values cache concurrency and prior plan]
    D -->|yes| F{collection scan}
    F -->|yes| G[test eligibility selectivity and index cost]
    F -->|no| H{keys or fetched documents excessive}
    H -->|yes| I[test bounds residual filter projection and multikey fan-out]
    H -->|no| J{sort spill or aggregation fan-out}
    J -->|yes| K[test pipeline order indexes and data model]
    J -->|no| L[test concurrency cache storage and network]
    G --> M[validate one reversible change]
    I --> M
    K --> M
    L --> M

In Practice

MongoDB’s query-plan documentation says candidate plans run during a trial, the winner is cached for later operations of the same plan-cache shape, and explain() bypasses that cache. It also documents that planCacheKey depends on both shape and available indexes. The derived control is to preserve historical and reproduced plan evidence separately.

The explain result reference documents plan trees, classic and slot-based output differences, IXSCAN, COLLSCAN, and explicit SORT. It warns that listed output fields are subject to change. A durable collector therefore normalizes a small stable contract while retaining raw, versioned evidence.

MongoDB’s profiler guidance explicitly warns about performance, storage, and disclosure risks. The profiler output exposes examined work, sort presence, and disk use. This supports shape discovery, but sampling and threshold configuration remain part of the evidence.

The aggregation optimizer documentation describes predicate movement and index opportunities for $match, $sort, selected $group patterns, and later collection-access stages. The documented pattern is to prove optimized cardinality flow, not merely rewrite pipeline text.

Remediation Options

Proven causeCandidate responseValidation
Missing or weak access pathbuild a targeted compound index using ESR or selective ERS tradeoffexamined work, sort, latency, write cost
Fetch dominatesnarrow projection or consider a justified covered indexdocuments examined, response contract, index size
Broad range dominatestighten application predicate, paginate by stable key, or redesign accessbounded keys and documents per request
Blocking sort or groupalign index and early filters, reduce input, or precompute justified resultsno unwanted sort or spill, correct output
$lookup fan-outindex eligible foreign predicate, reduce outer input, or reconsider embeddingforeign work and end-to-end correctness
Planner needs temporary restrictiontest hint; consider governed MongoDB 8.0 query settingsall representative values and rollback pass
Shape is operationally unsaferate-limit at application or use approved rejection controlrejected scope exact and service behavior acceptable

Index hints are diagnostic controls, not proof of a universal fix. MongoDB 8.0 query-setting index hints restrict the planner’s choices but do not guarantee index use; the planner may still select a collection scan. Index creation also consumes build resources and adds maintenance to every affected write.

Rollback Plan

Record original indexes, query settings, profiler configuration, application query, and baseline. Before dropping an index, hide it: MongoDB maintains a hidden index but excludes it from planning, allowing quick reversal. It still consumes write, memory, and disk resources.

For a new index, define build-window load limits, replication and disk stop conditions, and a drop procedure. For query settings, record the exact queryShapeHash, permitted indexes, owner, expiry, and removeQuerySettings rollback. For application or pipeline changes, use versioned rollout, correctness comparison, latency and resource validation, and rapid traffic reversal.

Rollback when the target shape improves but total write latency, cache pressure, disk use, replication lag, or another important shape regresses. Local query speed is not sufficient production proof.

Where It Breaks

Failure modeWhy the conclusion failsBetter boundary
Slowest sample equals biggest offenderIgnores frequency and cumulative workAggregate cost per shape and interval
IXSCAN equals efficientBounds, fetches, and fan-out may remain largeKeys, documents, returned rows, stages
One literal represents the shapeSkew and partial-index eligibility differRepresentative and adverse value buckets
Explain equals incident planIt bypasses the existing plan cacheHistorical summary plus controlled reproduction
Fixed JSON path parses every planEngines and versions change structureVersioned raw output and tolerant normalization
Move every $match manuallyOptimizer already performs eligible rewritesInspect optimized plan and stage cardinality
Add every recommended indexWrites, memory, storage, and build cost accumulatePortfolio review and end-to-end validation
LLM generates production DDLText cannot authorize capacity and correctness riskDBA-approved typed change with rollback

What the LLM Cannot Do

Explicit operational boundaries govern the LLM:

  • No Raw Customer Data: The LLM must not receive documents, profiler entries containing literal filter values, or currentOp output with embedded query predicates. Send normalized query shapes, planSummary, docsExamined/nReturned ratios, and timings.
  • No Autonomous Production Execution: The LLM cannot create or drop indexes, run moveChunk, alter the balancer, step down primaries, or change cluster configuration. It proposes; a human authorizes, applies, validates, and holds the rollback condition.
  • No Causal Conclusion Without Contradicting Evidence: Every hypothesis must cite both the telemetry that supports it and the telemetry that would falsify it. A ranked hypothesis with no disconfirming test attached is an assertion, not a diagnosis.
  • No Silent Gap-Filling: Where the evidence pack lacks a required signal, the LLM must name it explicitly under missing evidence rather than inferring a plausible value. “Not collected” and “collected and normal” are different findings and must never be merged.
  • No Change Without a Human Gate: Production changes require human authorization, a stated validation signal, and a defined rollback condition agreed before the change is applied.

What to Do Next

  • Problem: Individual slow operations and top-level stage names hide which MongoDB shape consumed total incident capacity and why.
  • Solution: Correlate normalized historical shapes with representative, bounded explain evidence; then trace keys, documents, fetches, sorts, spills, and aggregation fan-out.
  • Proof: Require the proposed change to reduce shape-level examined work and incident resources without unacceptable write, cache, disk, replication, or correctness regression.
  • Action: Build the shape-ranking collector first. For the highest correlated shape, preserve both historical plan identity and controlled explain evidence before proposing an index or pipeline rewrite.

Sources