Covered distinct and $group on sharded collections

Parent: Covered queries on sharded collections · Published reference · snapshot 2026-10-31

Source-anchored research for Covered distinct and $group on sharded collections. Run status: COMPLETED.

These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.

How it works

Indexes can cover distinct operations when all required fields are available from the index. For queries through mongos on sharded collections, coverage additionally requires the shard key in that index, including fields needed internally for sharding. An indexed distinct operation is therefore not automatically covered. (medium confidence) [source]
Verify coverage separately from DISTINCT_SCAN selection: run explain("executionStats"), inspect the shard execution plans for document fetches, and check documents examined. Index-only coverage means the operation needs no source documents; requesting an unindexed accumulator field can require document fetches even when a related distinct query is covered. (medium confidence) [source]
SERVER-110423 documents incorrect $group results when DISTINCT_SCAN used a sparse index and silently omitted the null group for missing fields. Its updated issue record lists fixes in 7.0.32, 8.0.20, 8.2.2 and 8.3.0-rc0. The issue recommends upgrading or selecting a collection scan or eligible nonsparse index as a workaround. This correctness hazard is separate from orphan filtering and is not limited to sharded collections. (low confidence) (verified as of 2026-10-31) [source]

Measurements and reference values

A 2016 first-person report describes distinct failing above 16 MB on a three-shard collection whose distinct field leads the shard key. Treat it as historical workload evidence of the result-size problem, not proof of a particular covered execution plan. (low confidence) (verified as of 2026-10-31) [source]

Problems, failure modes and limitations

Covered execution does not remove the distinct result-size limit: distinct returns its values in a single array subject to the maximum BSON size, and MongoDB recommends $group when that result is too large. (high confidence) [source]
The MongoDB 8.0 $group manual scopes its orphan warning to logically distinct-equivalent pipelines with $group at or near the beginning and no preceding $sort. It instructs users to inspect explain output for DISTINCT_SCAN. This is a versioned documentation caveat, not a claim that every $group plan is affected. (low confidence) [source]

Comparisons and alternatives

DISTINCT_SCAN can skip repeated index values instead of scanning every duplicate entry. It can outperform IXSCAN when many documents share a key, but scan parameters affect the comparison; the stage name alone does not establish a particular speedup. (medium confidence) [source]
SERVER-13116, concerning distinct shard awareness, and SERVER-42160, concerning $group DISTINCT_SCAN shard filtering, both list Fixed with fix version 8.2.0-rc0. These tracker records establish a version boundary to investigate, rather than a universal contemporary orphan-return guarantee. (medium confidence) (verified as of 2026-10-31) [source]
SERVER-55200 documents a MongoDB 4.4.3 sharded sort/match/group/first reproduction that chose IXSCAN with FETCH and shard filtering instead of DISTINCT_SCAN. The issue lists Fixed in 8.2.0-rc0, so old advice that this shape can never use DISTINCT_SCAN on shards is version-sensitive. (low confidence) (verified as of 2026-10-31) [source]
A pipeline such as {$group: {_id: "$field"}} produces one output document per distinct group key; its output _id holds the group key rather than necessarily the source document identifier. $group does not guarantee output order. (medium confidence) [source]
For first/last-per-group workloads, sorting and grouping on the same field, using only $first or $last accumulators, and providing a matching index can enable an index optimization. Treat this as an eligibility pattern rather than a guarantee for every sharded deployment; SERVER-55200 documents a historical sharded pipeline that failed to choose DISTINCT_SCAN. (medium confidence) [source]
SERVER-42160 records that historical DISTINCT_SCAN-based $group plans omitted SHARDING_FILTER, allowing stale orphan values after updates or deletes. Its record lists 8.2.0-rc0 as the fix version; related distinct sharding bug SERVER-13116 and sharded optimization issue SERVER-55200 also list 8.2.0-rc0. These issue records require version-specific interpretation rather than a timeless assertion that sharded distinct cannot filter orphans. (medium confidence) (verified as of 2026-10-31) [source]

Facts and statements

MongoDB documents that distinct operations can use indexes and can be covered; a bare {$group: {_id: "$field"}} can also produce DISTINCT_SCAN. These are plan possibilities, not a guarantee that every sharded distinct or grouping query is covered. (high confidence) [source]
For a pipeline that sorts and groups on the same field and uses only $first or $last accumulators, an index matching the sort can let MongoDB find each group's first or last document quickly. The documented example uses {year:1,title:1}; this general optimization still requires checking the actual sharded plan. (high confidence) [source]
Within a transaction on a sharded collection, the documented distinct method and distinct command are prohibited; use an aggregation pipeline with $group to retrieve distinct values instead. This transaction restriction does not prohibit all nontransactional sharded distinct operations. (low confidence) [source]
SERVER-42160 records that historical $group DISTINCT_SCAN plans omitted shard filtering and could emit stale orphan-derived values after updates or deletions. The issue lists a fix version of 8.2.0-rc0; its old description must not be presented as an unresolved defect in every current release. (low confidence) (verified as of 2026-10-31) [source]
SERVER-133187 describes sharded $unwind+$group DISTINCT_SCAN support as an unresolved backlog task with no fix version. Its August 2026 example uses a compound index containing the shard-key prefix and a later multikey field; do not extrapolate unsharded unwind/group optimization to this sharded case. (low confidence) (verified as of 2026-10-31) [source]
For ApsaraDB for MongoDB 5.0 or earlier, Alibaba documents inspecting SHARDING_FILTER.chunkSkips in executionStats to identify orphan documents. This is supporting diagnostic context for the historical shard-filtering defect, not a distinct-specific performance guarantee or a prescription for every newer version. (low confidence) [source]
The distinct method documentation warns that sharded distinct may include orphaned data. The MongoDB 8.0 $group documentation similarly warns about orphaned results for distinct-like grouping plans using DISTINCT_SCAN; rewriting distinct as $group is not, by itself, proof that orphans are excluded. (medium confidence) [source]

Children

Version-dependent shard filtering within DISTINCT_SCAN (frontier)
First/last-per-group index eligibility (frontier)
Sparse-index missing-field grouping correctness (frontier)

Frontier under this node: First/last-per-group index eligibility, Shard-aware DISTINCT_SCAN prefix eligibility and seek progression (3.75), Sparse-index missing-field grouping correctness, Version-dependent shard filtering within DISTINCT_SCAN

← the whole tree · 3D view· how to read this page