ESR guideline and $in placement in compound indexes
Parent: SORT_MERGE and $in sort expansion · Published reference · snapshot 2026-10-30
↓ Facts as markdownall context files
Source-anchored research for ESR guideline and $in placement in compound indexes. 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 ESR baseline places equality keys first, sort keys next, and range keys last when preserving index sort order is the priority. (high confidence) [source]
Problems, failure modes and limitations
- The documented 201-or-more case treats $in as range-like for sorting: subsequent index keys cannot supply the requested sort. Place typically large lists with range fields when designing for ESR. (medium confidence) (verified as of 2026-10-14) [source]
- The 201-element boundary is not guaranteed across versions; the historical SERVER-14034 fix raised the scan-expansion limit from 50 to 200 in MongoDB 2.6.4. (medium confidence) [source]
Comparisons and alternatives
- ERS can outperform ESR when a selective range reduces enough work to justify a blocking sort; ESR is a guideline rather than a universal optimum. (medium confidence) [source]
- Validate index placement with explain('executionStats') on representative queries and list sizes. Inspect SORT versus SORT_MERGE and compare keys and documents examined instead of assuming that IXSCAN alone proves sort support. (medium confidence) [source]
- For sort-bearing queries, place typically small $in lists with equality predicates and typically large lists with range predicates; verify the plan rather than treating $in as unconditional equality. (medium confidence) [source]
- Compare candidate index orders using hint() and explain('executionStats'); inspect SORT versus SORT_MERGE and compare keys/documents examined with rows returned. (medium confidence) [source]
Facts and statements
- Equality keys can appear in any relative order for a query that constrains all of them to exact values. (medium confidence) [source]
- For eligible sorted queries, the documented small-list case is fewer than 201 $in elements: ordered index scans can feed SORT_MERGE, making the $in key equality-like before the sort keys. (medium confidence) (verified as of 2026-10-14) [source]
- SERVER-14034 documents multiplication across $in prefixes: ten values on a and six on b require 60 scans for sorting c under {a:1,b:1,c:1}. That exceeded the historical 50-scan cap; small individual lists therefore did not guarantee expansion. (low confidence) [source]
- $in alone performs equality matches; with sorting, short lists can be expanded and combined through SORT_MERGE, while long lists behave like ranges for index ordering. (medium confidence) [source]
- The MongoDB guide documents fewer than 201 $in elements as expandable with sort, and 201 or more as range-like, preventing subsequent index fields from supplying sort. It explicitly warns this cutoff can vary across versions. (low confidence) (verified as of 2026-10-14) [source]
Children
- Scan-expansion limits across multiple $in predicates (frontier)
- ESR versus ERS selectivity trade-offs (frontier)
Frontier under this node: ESR versus ERS selectivity trade-offs, Scan-expansion limits across multiple $in predicates