DISTINCT_SCAN for distinct command
Parent: MongoDB Advanced Explain: Aggregation, Sharded Plans, Loose Index Scans and the 8.3 Cost-Based Ranker · Published reference · snapshot 2026-10-21 · skill ranked-a146e5ac
↓ Facts as markdownall context files
Source-anchored research for DISTINCT_SCAN for distinct command. 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
- MongoDB's current query header exposes tryGetQuerySolutionForDistinct, which attempts a DISTINCT_SCAN plan for a distinct command when the arguments allow one; MongoDB's planner issue describes the stage as skipping duplicate index keys with index seeks. (high confidence) (verified as of 2026-10-21) [source]
- MongoDB documents that distinct operations can use indexes when possible, not that every distinct command uses an index or DISTINCT_SCAN. A MongoDB planner issue describes multikey compound-index eligibility as depending on the target key's bounds and whether filtered prefix fields share multikey path prefixes. (medium confidence) (verified as of 2025-02-12) [source]
- An index containing the distinct field does not guarantee a DISTINCT_SCAN. MongoDB's manual says distinct operations use indexes when possible, and SERVER-91924 shows distinct('x', {y:1}) with an index on {x:1, y:1} selecting COLLSCAN in a MongoDB 8.1.0-alpha reproduction; the issue was unresolved at the current Jira check. (medium confidence) (verified as of 2026-10-21) [source]
Comparisons and alternatives
- A MongoDB Jira reproduction updated 2024-05-15 reports that MongoDB 6.0.15 and 7.0.10 used FETCH/IXSCAN instead of DISTINCT_SCAN for a query on array-valued dotted field b.c, while the scalar-field reproduction used the index; this is a versioned example, not a universal current rule. (low confidence) (verified as of 2024-05-15) [source]
- A MongoDB issue reproduced distinct returning [1, 2] under COLLSCAN and [null, 1, 2] after adding an index for documents missing the field. SERVER-85298 was closed as a duplicate of SERVER-14832; that root issue remains unresolved and lists 2.6.0 and 2.6.3 as affected, so this is a version-scoped test caveat rather than evidence of current-release behavior. (medium confidence) (verified as of 2026-10-21) [source]
Facts and statements
- For an index {a: 1, b: 1}, a DISTINCT_SCAN can return one entry per (a, b) key, so b may repeat across different a values; the distinct('b') command can reduce the scan output to unique b values. MongoDB also documents that distinct treats each element of an array-valued field as a separate value. (high confidence) [source]
- The stage label alone does not establish elapsed-time performance. A MongoDB planner issue says scan efficiency depends on the distinct field's position and the number of unique prefixes before it; a Database Administrators Stack Exchange answer also conditions DISTINCT_SCAN's advantage on duplicate frequency and index bounds. (medium confidence) [source]
- MongoDB describes DISTINCT_SCAN as an optimized specialization of an index scan for a subset of distinct and distinct-like aggregate operations; it uses index seeks to skip duplicate keys, while IXSCAN scans index entries more generally. (high confidence) [source]
- SERVER-45689 says eligible DISTINCT_SCAN candidates can differ in efficiency based on the distinct field's position in the index key and the cardinality of preceding key fields. The issue lists its multi-planner candidate-generation and evaluation fix for 8.2.0-rc0. (low confidence) (verified as of 2026-10-21) [source]
- Do not rank plans by the DISTINCT_SCAN label alone: a DBA Stack Exchange answer notes that index bounds affect scan work and recommends checking explain execution statistics; MongoDB's candidate-planning issue also describes DISTINCT_SCAN candidates with different efficiencies. (medium confidence) [source]
Children
- No children recorded.