ERS exception for selective range predicates
Parent: MongoDB Compound Index Design: ESR, Selectivity, Covering and Multikey Bounds · Published reference · snapshot 2026-10-21 · skill ranked-29798f23
↓ Facts as markdownall context files
Source-anchored research for ERS exception for selective range predicates. 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
- The selective-range exception changes only the order of the Range and Sort keys: keep Equality fields first, then use ERS when the range narrows results enough to make sorting the smaller set worthwhile; otherwise favor ESR when avoiding an in-memory sort matters. (high confidence) [source]
Problems, failure modes and limitations
- When a range key precedes a distinct sort key, the compound index generally cannot provide the requested global sort order. ERS trades index-provided order for a blocking in-memory sort after range filtering; ESR avoids that sort but can scan more index keys. (high confidence) [source]
Comparisons and alternatives
- MongoDB's manual describes the condition qualitatively as a range predicate that is 'very selective' and sets no numeric cutoff on this page. An independent worked example says the winning order depends on the data, so assess the tradeoff on representative data instead of treating ERS as a fixed selectivity threshold. (medium confidence) [source]
Facts and statements
- In one synthetic 200,000-order example, for total >= 499, the ESR index scanned 2,338 keys to return one order while the ERS index scanned one key and sorted that one result; this illustrates a possible ERS win, not a portable performance ratio. (low confidence) (verified as of 2023-07-09) [source]
Children
- No children recorded.