skip to content

Why does MongoDB's planner rarely choose index intersection with AND_SORTED or AND_HASH stages?

level: seniorimportance: nice to knowfreq 24%

answer

  1. Two scans, one result set
  2. Each side reads what the other will discard
  3. Ordering does not survive the combine
  4. The prescription is one index, not two

basics

~20 s

Intersection combines results from two index scans, then still fetches documents, and it cannot supply a sort order. A single compound index usually wins the planner's scoring because one scan yields the matching keys directly and can also satisfy the sort.

solid answer

~50 s

MongoDB *can* combine two indexes for one query — `explain()` shows an **`AND_SORTED`** or **`AND_HASH`** stage over two `IXSCAN` nodes — but the planner picks it rarely. The reason is cost: each index is scanned over its own predicate, so both scans read entries that the other predicate will discard, the results must be intersected, and the surviving record locations still need a `FETCH`. A compound index over the same fields walks one contiguous range whose keys already satisfy every predicate, and it can additionally deliver the query's sort order — which an intersection generally cannot, so an intersected plan usually drags a blocking `SORT` along. Since the planner scores candidates by measured work during a trial period, the compound plan normally wins outright. The practical lesson is the one interviewers want: do not create one single-field index per query field hoping they will be combined; build the compound index the query shape needs.

code

javascript · 7 lines
javascript
// two single-field indexes: planner may intersect, usually poorly
db.orders.createIndex({ status: 1 });
db.orders.createIndex({ region: 1 });

// one compound index shaped to the query: one narrow scan
db.orders.createIndex({ status: 1, region: 1, createdAt: -1 });
db.orders.find({ status: "ACTIVE", region: "EU" }).sort({ createdAt: -1 });

go deeper

for a junior

Know that MongoDB normally uses one index per query and that combining several single-field indexes is not the way to make a multi-field filter fast.

for a middle

Explain the mechanics: two index scans, an AND_SORTED or AND_HASH combine, a surviving FETCH, and why one compound index reads far fewer keys.

for a senior

Argue it from the planner's cost model and the sort requirement, and show the fix — the compound index in equality-sort-range order plus dropping the redundant single-field indexes.

for a principal

Own the index inventory: how many indexes a hot collection can afford, which compound keys serve whole families of queries through their prefixes, and the write cost of every index kept for a rare query shape.

## The mechanism Given `find({ status: "ACTIVE", region: "EU" })` with separate indexes `{ status: 1 }` and `{ region: 1 }`, MongoDB has three broad options: 1. Scan `{ status: 1 }` for `"ACTIVE"`, fetch each candidate document, and apply `region` as a residual filter. 2. Scan `{ region: 1 }` for `"EU"`, fetch, and filter on `status`. 3. **Intersect**: scan both indexes and keep only the record locations that appear in both, then fetch. Option 3 appears in `explain()` as an `AND_SORTED` stage (when both scans return record identifiers in a comparable order, allowing a merge) or an `AND_HASH` stage (build a hash of one side's identifiers, probe with the other), sitting above two `IXSCAN` nodes and below a `FETCH`. ## Why it loses **Both scans do throwaway work.** The `{ status: 1 }` scan reads *every* `ACTIVE` key, including all the non-EU ones; the `{ region: 1 }` scan reads every `EU` key including the inactive ones. The intersection is only as small as the final result, but the reading is proportional to each predicate individually. A compound `{ status: 1, region: 1 }` scan reads only keys matching both — the equality on `status` pins a block and the equality on `region` narrows within it. **The fetch is still there.** Intersection produces record locations, not documents, so the `FETCH` remains. Intersection saves the *filtering* fetches, not all of them. **It cannot serve the sort.** An intersected stream is not in any single index's key order once merged (and `AND_HASH` explicitly destroys ordering), so a query with `sort()` normally gains a blocking `SORT` stage on top of an intersected plan. A compound index built in equality-sort-range order avoids that sort entirely, which is often the single biggest difference in the plans' scores. **The planner scores by measured work.** Candidate plans are executed side by side for a short trial and scored on how productive they are — how many documents they advanced per unit of work — with tie-breaking preferences that disfavour blocking stages. An intersected plan that reads two broad key ranges and then sorts scores poorly against a compound plan that reads one narrow range in order. ## When you actually see it Intersection shows up mostly on ad-hoc queries where no compound index exists and both predicates are individually selective — each side returns a modest number of entries and the overlap is smaller still. That is a real but narrow window, and it is the planner's fallback rather than a design target. ## The design lesson The reason this question is asked is that engineers coming from a mental model of "index every column and let the optimizer combine them" build a pile of single-field indexes and are surprised that queries stay slow. In MongoDB the productive move is almost always a **compound index shaped to the query**: equality fields first, then the sort fields, then the range field. One well-shaped compound index typically replaces several single-field ones, and dropping the redundant ones gives back write throughput and cache space, because every index must be maintained on every write that touches its fields. A compound index also serves any query matching a **leading prefix** of its keys, so `{ status: 1, region: 1, createdAt: -1 }` also serves queries on `status` alone and on `status` plus `region`. That prefix property is what makes a small set of compound indexes cover a family of query shapes — the coverage people mistakenly hope intersection will provide. ## Confirming it in explain To see whether intersection was even a candidate, run `explain("allPlansExecution")` and inspect `rejectedPlans` alongside the winner: if an `AND_SORTED` or `AND_HASH` plan is listed there, the planner considered and rejected it, and the per-plan statistics show why. If it is not listed at all, the shape did not qualify. Either way, the response to a slow multi-predicate query is to build the compound index and re-measure `totalKeysExamined` against `nReturned` — not to hope the planner will combine what you already have. ## Answering in an interview Name the stage names, explain that each scan does work the other predicate throws away, note that the fetch survives and the sort usually cannot be served, and land on the prescription: build the compound index and drop the redundant single-field ones.

  • Which stage names in explain() reveal that two indexes were combined?
    AND_SORTED and AND_HASH, each sitting above two IXSCAN nodes. AND_SORTED merges two streams of record identifiers that arrive in comparable order; AND_HASH builds a hash from one side and probes it with the other. In both cases a FETCH still sits above, because intersection yields record locations rather than documents.
  • If a compound index is usually better, why does MongoDB implement intersection at all?
    It is a fallback for query shapes nobody indexed for. When two predicates are each individually selective and no compound index exists, intersecting two narrow scans can beat scanning one index broadly and filtering after the fetch. It buys some resilience on ad-hoc queries, but it is not something to design a schema around.
  • What should you build instead when a query filters on three separate indexed fields?
    A single compound index ordered by equality fields first, then any sort fields, then the range field. It reads one contiguous key range that satisfies all the predicates, can supply the sort order, and — because a compound index also serves queries matching a leading prefix of its keys — usually lets you drop some of the single-field indexes and recover write throughput.

saying these in an interview costs you the question

  • Assumes MongoDB routinely intersects several single-field indexes
  • Creates one index per queried field expecting them to be combined
  • Thinks an intersected plan can also satisfy the query's sort
  • Believes intersection removes the need for compound indexes
  • Says intersection avoids the FETCH stage entirely

context

resolving links…