Build1 publisher3 min readPublished
MongoDB's planner drops index use for any $match placed after $group
A dev.to walkthrough puts the cost of an aggregation pipeline in stage order, index coverage and a 100 MB per-stage memory ceiling. The published text breaks off before the sharding section its own outline promises.
The Engineer · Build desk
What happened
- The post classifies $group, $facet, $bucket and an unindexed $sort as blocking stages, which must consume their entire input before emitting anything downstream.
- Grouping on an unindexed high-cardinality field builds an in-memory hash table of all group keys, which the post says can exhaust the same 100 MB per-stage limit.
- The published text breaks off mid-sentence inside the indexing section, before the sharding, case-study and profiling sections listed in its own outline.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- constraint The first $group sets a boundary in the pipeline. Every filter written after it runs over intermediate documents, so the ordering decision binds the plan before a document is read.
- decision Teams filtering on a computed total choose between recomputing it for every document at read time and storing it as an indexed field. The post recommends the stored field, and the write path pays for it.
- cost In the post's account, allowDiskUse decides whether a large sort completes at all. That makes it a reliability setting owned by whoever runs the cluster.
- capability One index on the sort key converts a blocking $sort into a streaming one and takes that stage out of reach of the per-stage memory ceiling.
Ordering is settled at plan time, and the dev.to post, published under the handle tamizuddin and originally on tamiz.pro [17], states the rule plainly: the query planner can use an index for a `$match` stage only if that `$match` appears before any blocking stage [3]. `$group` is blocking [2]. So no `$match` placed after the first `$group` can be served by an index [3]; it filters intermediate documents that no index describes.
The post's own example shows the price. Its anti-pattern projects a per-document total, matches `total > 1000`, then groups by `customerId`, and the `$project` runs on every document in the collection before anything is discarded [6]. The rewrite it recommends puts `{ $match: { status: "completed" } }` first, keeps the computed comparison after the `$project`, and groups last [7]. Adding a stage made it faster: the recommended version has four stages against the anti-pattern's three [2].
`$sort` is the only stage that appears on both of the post's lists [1]. It streams when an index can supply the order [1] and blocks when nothing can [2]. Without a matching index it loads all documents into memory, and if the result exceeds 100 MB the pipeline fails unless `allowDiskUse` is enabled [5]. A high-cardinality `$group` reaches the same ceiling by another route, building an in-memory hash table of every group key [8].
`$lookup` is the stage where a missing index costs the most: the post says it performs a nested loop join by default, and that without an index on the foreign collection's join field each lookup degenerates into a collection scan; wrap that lookup in a `$unwind` or a `$group` and the cost multiplies even when the index exists [9].
The post does not name a MongoDB version or build [18]. That leaves the 100 MB failure anchored to one configuration. Whether the limit and the `allowDiskUse` behaviour hold on the deployment you actually run is not something the write-up settles.
The outline promises three sections the text does not reach. Sections 6, 7 and 8 are listed as sharding considerations, a real-world case study, and monitoring and profiling [10]. The published text stops mid-sentence inside section 4, on indexing strategies, at an example beginning `{ $match: { status: "shipped", region: "us-east" } }` [11]. Shard-key alignment would plausibly be a fourth decision affecting cost at the scale the post opens on, collections growing into the hundreds of millions or billions of documents [12], and the available text stops before it.
The post's cheapest fix needs no index at all: it ties fields that are never used downstream to document size, memory consumption and network I/O, and recommends pruning them early to shrink the working set [13].
What to watch
- Whether the author publishes sections 6 to 8, since the case study is where cardinality, index definitions and shard keys would become checkable.
- Whether a version and allowDiskUse default get stated, which determines if the 100 MB failure transfers to other deployments.
- Any measurement of the write-path cost of the maintained, indexed total field the post recommends.