AND_SORTED vs AND_HASH plan stages
Parent: MongoDB Compound Index Design: ESR, Selectivity, Covering and Multikey Bounds · Published reference · snapshot 2026-10-22 · skill ranked-40493c90
↓ Facts as markdownall context files
Source-anchored research for AND_SORTED vs AND_HASH plan stages. 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.
Structure and components
- AND_SORTED's “sorted” describes the RecordId order of its child streams, not the query's requested sort order. The MongoDB 6.0 manual allows intersection for some sorted queries when an index used for a predicate also supplies part of the sort; intersection does not apply when the sort needs an index wholly separate from the filter. (high confidence) [source]
How it works
- When explain shows AND_SORTED or AND_HASH in a plan, MongoDB is using index intersection. Inspect the winning plan and execution statistics to see which intersection strategy actually ran. (high confidence) [source]
- Index intersection is a candidate plan, not a guarantee: MongoDB can choose one IXSCAN instead. In source revision 35647d07, the plan ranker breaks ties in favor of single-index plans because intersection can examine more index keys; a compound index can constrain both predicates in one index traversal. (high confidence) [source]
- AND_SORTED assumes every child produces RecordIds in sorted order. It advances child streams toward a target RecordId and emits an ID when the required children reach that same ID. (high confidence) (verified as of 2026-10-22) [source]
- An intersection stage appears only when the planner selects an intersection candidate. In Franck Pachot's 100,000-document example, MongoDB generated an AND_SORTED candidate but selected a single-index plan. (high confidence) [source]
Problems, failure modes and limitations
- The current classic AndHashStage defaults to a 32 MiB buffer limit and raises QueryExceededMemoryLimitNoDiskUseAllowed when buffered usage exceeds the configured limit. (low confidence) (verified as of 2026-10-22) [source]
Facts and statements
- AND_SORTED and AND_HASH both intersect RecordId sets from separate index scans. AND_SORTED advances input streams that share RecordId order and can merge them incrementally. AND_HASH builds a hash table from one child scan and probes it with the other, so it must consume the build-side scan before it can return matches. (high confidence) [source]
- MongoDB 6.0 documentation states hash-based intersection is disabled by default and sort-based intersection is disfavored in plan selection. Franck Pachot's article, edited 2026-03-09, also records the hash-intersection setting as false in its tested environment. Verify server version and internal settings before expecting AND_HASH. (medium confidence) (verified as of 2026-03-09) [source]
- MongoDB issue SERVER-14961 records an index-intersection ranking regression specific to MongoDB 2.6.4 and says the fix shipped in 2.6.5; this historical incident does not establish current-version planner behavior. (low confidence) [source]
- AND_SORTED and AND_HASH implement index intersection by comparing RecordIds from their child stages and retaining matches shared across the children. (high confidence) (verified as of 2026-10-22) [source]
- AND_HASH builds a RecordId-keyed in-memory map from preceding children, then probes the final child. With two children, it reads the first child to completion before it can return matches from the second; this makes the stage blocking and its memory use proportional to buffered results. (high confidence) (verified as of 2026-10-22) [source]
- The AND_SORTED ordering criterion applies to RecordIds. A query's requested field sort must be evaluated separately from the stage name. (high confidence) [source]
Children
- No children recorded.