Capire i query plan di MongoDB

Context

mongoose-lens does something deliberately narrow: it intercepts slow Mongoose queries, runs explain() on them, flags two stages — COLLSCAN and a blocking SORT — and proposes a compound index ordered by the ESR rule. Building that meant deciding which signals in a query plan are safe to act on automatically and which need a human. This note is the reasoning behind those choices, and where the approach runs out of road. The index-selection mechanics are in the companion note; here the focus is the plan itself and the limits of reading it.

Scope: single-collection reads, WiredTiger, MongoDB 6.x/7.x (core mechanisms re-checked on 8.0.16, classic engine — unchanged). Not sharding, not the aggregation framework beyond $match / $sort. Everything here is plan-reading; the parts that need a benchmark to confirm are marked as such.

Question

What can you actually conclude from a single explain() output, and what are you only guessing at?

• How does MongoDB arrive at the plan it runs, and how stable is that choice?
• Which stages are unambiguous problems, and which are "it depends"?
• If a heuristic suggests an index from one plan, what could that suggestion break elsewhere?

Investigation

The method here was to read plans, not to benchmark — so most of what follows is mechanism, with the empirical parts called out explicitly.

• Getting a plan. db.coll.find(query).explain("executionStats") returns queryPlanner (candidate + winning plans) and executionStats (what actually happened for that run). mongoose-lens keys off executionStats because the stage tree alone doesn't tell you how bad a scan was.
• Reading the winning plan. It's a tree of stages executed leaves-first. The ones that matter: IXSCAN (an index was used; indexBounds shows how tight), FETCH (documents pulled by _id after an IXSCAN), COLLSCAN (every document examined), SORT (a blocking, in-memory sort — no index could yield the sort() order in one directional scan, so the rows are buffered and ordered in memory, spilling to disk past the sort memory limit; a limit bounds it to a top-k, and if the sort field is in the scanned index it sorts index keys rather than whole documents).
• Numbers to compare. nReturned vs totalKeysExamined vs totalDocsExamined. keysExamined ≫ nReturned usually means the index scan is wide — but discount a multi-interval $in (one boundary probe per interval) and a multikey index (one key per matching array element), where the gap is normal, not waste. docsExamined ≫ nReturned is the sharper signal: the FETCH is discarding rows a residual filter could not push down. executionTimeMillis is the headline and the most variable number.

Done (companion lab, Experiment 06): swept a predicate from very selective to matching the whole collection and recorded indexBounds / keysExamined at each step. keysExamined tracks the interval width; the switch to COLLSCAN never happens on selectivity grounds alone — see the note below.

Observations

Working model — the trial/cache mechanics, the COLLSCAN behaviour and the blocking-SORT behaviour are all confirmed by testing:

• MongoDB enumerates candidate plans from indexes whose key pattern is compatible with the query, runs them in a short trial, and keeps the one that made progress with the least work. The winner is cached per query shape (the query with its values abstracted away).
• explain() is cache-free: it re-runs the planner on every call and reports the plan it would pick now, ignoring any cached entry (isCached stays false). So its plan is stable for fixed data and indexes — but it can still mislead two ways. It re-plans as the data or the specific values change, so a different parameter can yield a different explain() plan; and a real query may run a cached plan — chosen for an earlier data distribution and reused value-blind — that explain() will never show you. (Checked against system.profile in the companion lab, Experiments 04–09.)
• COLLSCAN and blocking SORT are the two stages almost always worth acting on: the first means no index was usable at all, the second means no index yields the requested order in one directional scan. COLLSCAN is structural — a COLLSCAN at 1k rows is a COLLSCAN at 1M. A blocking SORT is mostly structural but not fully data-independent: for a query that is {range} + sort, the planner keeps a tight-filter + SORT plan only while the range is very selective (~2% in testing) and switches to a wider sort-supplying scan — with no SORT — once it isn't. Flagging the stage is still safe; predicting whether it appears from the query text alone is not.
• IXSCAN is not automatically fine. An index scan with keysExamined far above nReturned, or followed by a large FETCH, is still a bad plan.

Done (companion lab, Experiments 04–05): cold vs warm cache and planCacheClear behaviour — a plan is chosen by a trial, cached per shape, and then reused value-blind until the accumulated work overruns a decisionWorks × ratio budget and the shape is replanned. explain() never reads the cache, so "data vs cache" is only visible in system.profile (fromPlanCache) or the mongod log. Done (companion lab, Experiment 09): a query sorting { a: 1, b: 1 } against an index { a: 1, b: -1 } does force a blocking SORT — but { a: -1, b: 1 } (the index's exact mirror) does not: the b-tree is walked backward. A single reversed sort key is always free; only a partial direction conflict on a compound sort needs the SORT. SORT cost is rows_in × row_size and scales linearly with the feeding scan's keysExamined.

Technical details

Query shape → index eligibility. An index is a candidate when its key prefix matches the query's equality and range predicates and, ideally, its later keys match the sort. Equality predicates collapse the scan to a contiguous stretch of the b-tree; a range predicate opens it; keys after a range key can't be used to narrow further or to provide order.

ESR. Order compound index keys as Equality, then Sort, then Range: equality fields first pin the scan to one contiguous stretch; sort fields next let the index return rows already ordered, removing the blocking SORT; range fields last, because everything after a range is unusable for equality or ordering. It's a heuristic, not a law — it degrades with two independent range predicates, with $in (which sits between equality and range), and when a covered query changes the trade-off.

Why COLLSCAN / SORT are the automatable signals. A COLLSCAN is a COLLSCAN at 1k or 1M documents — fully data-independent. A blocking SORT buffers its input (a limit bounds it to a top-k; the input is index keys if the sort field is in the scanned index, otherwise whole documents) and its severity scales with the row count. Its appearance is mostly structural, with one data-dependent exception — a {range} + sort query loses the SORT once the range stops being selective. Either way, once the stage is in the plan a heuristic can name it and propose an ESR-shaped index without a benchmark.

Recognising a potentially problematic query, from the plan alone:

• COLLSCAN on a collection that isn't tiny;
• a blocking SORT stage (especially SORT above a FETCH — it is ordering whole documents, not index keys);
• totalKeysExamined ≫ nReturned (wide index scan — after discounting a multi-interval $in and multikey de-duplication, where the gap is expected);
• totalDocsExamined ≫ nReturned (wasteful FETCH — rows dropped by a residual filter);
• $or where one branch has no usable index (can force a COLLSCAN for the whole query);
• a compound sort whose per-key directions are a partial mismatch with the index (e.g. sort {a:1, b:1} on index {a:1, b:-1}) — a single reversed key, or a whole-pattern reversal, is served by a backward scan and is fine.

Practical implications

• A tool can safely flag a COLLSCAN or a blocking SORT that is in a plan and propose an ESR index — the diagnosis is reliable once the stage is there. It should not silently trust IXSCAN (check the examined-vs-returned ratio), nor promise that a {range} + sort query will keep its SORT — that flips with the range's selectivity.
• An index suggestion is a starting point for a human, not a migration. It has a write cost on every insert/update, it takes cache RAM, it may duplicate the prefix of an existing index, and it can shift the planner onto a worse plan for a different query shape.
• Because the plan cache exists, "it was fast before" is not evidence. Re-run explain() now, and again after the data grows or its distribution shifts.
• A slow-query interceptor never sees the queries just under the threshold, or the rare-but-catastrophic ones. Sampling only the slow queries is a biased view of the workload.

Limitations

A heuristic that reads one explain() and suggests an ESR index has real blind spots:

• One sample, one parameter set. explain() is a single execution with specific values against the current data. A different parameter, date range, or skew you didn't hit can produce a completely different plan. The suggestion is fitted to one point.
• No write-side view. It sees reads. It can't weigh the suggested index against write amplification, index build time on a live primary, or cache pressure.
• Redundancy blindness. Without inspecting the existing indexes it may propose something already covered by an existing compound index's prefix.
• Global effects. An index that fixes query A can make the planner choose a worse plan for query B — same shape, different selectivity.
• Plan-cache coupling. The slow execution that triggered the tool may have run on a stale cached plan, but the explain() the tool then runs is cache-free and shows a freshly planned one — so the two can disagree, and the real cause (a cache entry that outlived its data) never appears in the explain() output. planCacheClear plus a re-run is what exposes it.
• Multikey and nested paths. Arrays change index-bounds semantics: a two-sided range ({$gte, $lte}) on an array field is not intersected into one interval, $all is not an index intersection, and a compound index turns multikey the moment one key is an array. A suggestion derived from a scalar reading of the query can be wrong for a multikey field. (Companion lab, Experiment 08.)
• $or, $in, negation. ESR ordering and "just add the index" both get shakier here, and a simple reader can miss that one $or branch is unindexed — which forces a COLLSCAN for the whole query. (Companion lab, Experiment 07.)

TODO: verify with benchmark — for a query with an obvious COLLSCAN, apply the ESR-suggested index and measure executionTimeMillis and totalDocsExamined before and after at ~1e4 / 1e5 / 1e6 documents. This is the one number that would justify the suggestion. TODO: verify with benchmark — construct a case where an index that fixes query A regresses query B (same shape, different selectivity), to quantify the "global effects" risk.

Takeaways

• A query plan is chosen by trial and cached per shape. explain() itself is cache-free and re-plans every call, so it is stable for fixed data and indexes — but a real query may run a different, cached plan it will not reveal.
• COLLSCAN and blocking SORT are safe to flag automatically once they are in the plan — but a SORT can appear or vanish with the range selectivity of a {range} + sort query, so don't predict it from the query text. IXSCAN needs the examined-vs-returned check before you trust it.
• ESR is a reasonable default order for a suggested compound index, and a heuristic, not a rule.
• A plan-reading tool like mongoose-lens is a detector, not a fixer: it narrows where to look. The index decision still needs a human who can see the write cost, the existing indexes, and the other queries.
• Every claim in this note with a number attached is marked TODO: verify with benchmark until it's been run. The companion lab below has since discharged several; the ones still open are still marked.

Evidence — companion lab

The claims here were later checked in a small, reproducible lab: mongo-query-lab — numbered experiments on a deterministic seeded dataset (same seed → same data), each one running explain("executionStats") with no hint() and committing the raw output under results/. MongoDB 8.0.16, classic engine, single node. It is plan- and counter-based, not a latency benchmark, and still in progress (the aggregation experiments are not done yet).

Relevant so far:

• 01 — COLLSCAN vs IXSCAN, 06 — Index bounds and the cost of a predicate: keysExamined is index-bound-traversal work; equality → point bound, range → interval. The switch to COLLSCAN is binary on "does a usable index exist", not a selectivity threshold — a range matching the whole collection still used the index.
• 04 — Plan cache and query shapes, 05 — Selectivity and cached-plan tolerance: per-shape caching, value-blind reuse, and the decisionWorks × ratio replan frontier. explain() is cache-free and matches a real cache-free execution's counters.
• 07 — $or / SUBPLAN: $or of equalities on one field is canonicalised to $in; across fields it is SUBPLAN → OR with per-branch IXSCAN and record-id dedup; one unindexed branch forces a COLLSCAN for the whole query, even if that branch matches nothing.
• 08 — Multikey indexes and array bounds: multikey de-duplication (keysExamined > docsExamined is normal), the un-intersected two-sided range, $elemMatch vs dotted paths, $all is not an index intersection, and covering the scalar prefix of a multikey index.
• 09 — SORT, range bounds, and the plan cache: an ESR index removes the blocking SORT and the planner prefers it at equal keysExamined; a single reversed sort key (or a whole-pattern reversal) is served by a backward scan, a partial compound-direction conflict is not; SORT cost is linear in rows fed in, and sits below the FETCH when the sort field is in the index; for a {range} + sort query the planner drops the SORT for a wide streaming scan at ~2% selectivity, accepting several times more keysExamined.