Rooted $or subplanning and SORT_MERGE
Parent: SORT_MERGE and $in sort expansion · Published reference · snapshot 2026-10-31
↓ Facts as markdownall context files
Source-anchored research for Rooted $or subplanning and SORT_MERGE. 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.
Definitions
- An empty rejectedPlans array is not sufficient evidence that branch candidate trials never happened: SERVER-19350 documents subplanner explain output omitting rejected alternatives, while the r8.0.0 code explicitly runs branch MultiPlanStage selection. (medium confidence) [source]
Structure and components
- SORT_MERGE merges ordered index streams. The tagged MongoDB subplanning code separately selects plans for rooted $or branches and contains branch cache machinery. (medium confidence) [source]
How it works
- Rooted $or subplanning plans the OR branches independently and combines their index choices into one executable plan; MongoDB 8.0.0's SubplanStage reads active branch cache data and uses MultiPlanStage when branch selection needs trials. (medium confidence) [source]
- MongoDB 8.0.0 can fall back from branch subplanning to planning the whole query when subquery planning fails or composite selection returns NoQueryExecutionPlans; the fallback can use a single solution or MultiPlanStage. Other composite-selection errors propagate. (low confidence) [source]
- The AWS/Miridih case study dated 2026-04-22 reports MongoDB merging indexed type branches with SORT_MERGE for a parent/type/date query, while their DocumentDB deployment sorted the combined results in memory. They split type queries and merged in the application. This is a workload-specific migration observation, not evidence about MongoDB's replanReason. (low confidence) (verified as of 2026-04-22) [source]
- Index-supported branch ordering still depends on index design: compound sort directions must match the index pattern or its complete inverse, and sorting on a non-prefix subset requires equality predicates on preceding index keys. SORT_MERGE is not guaranteed merely because every branch has an index. (medium confidence) [source]
- SERVER-91095 describes a historical SBE-subplanning special case: branch entries recovered from the classic cache carry a distinguishing cache-key byte and store neither works nor reads, because rooted-$or replanning was unsupported in that path. Treat this as the issue's implementation context, not a universal statement about every later version. (low confidence) (verified as of 2024-10-15) [source]
Problems, failure modes and limitations
- Index direction and collation deserve explicit checks when interpreting SORT_MERGE. SERVER-24027 records an old descending-pagination planning failure, fixed in 3.4.2/3.5.2; SERVER-51120 records a distinct historical collated-key merge correctness bug with patch fixes. Neither ticket establishes a current defect in modern releases. (medium confidence) [source]
- In r8.0.0, failed branch planning or failure to obtain a composite solution with NoQueryExecutionPlans can fall back to whole-query planning. Therefore a SUBPLAN wrapper alone does not establish that independent branch planning succeeded. (medium confidence) [source]
- The 2018 Database Investigator case contains SUBPLAN with SORT_MERGE over three IXSCAN branches, yet attributes most initial latency to FETCH. An ordered merge plan can therefore coexist with slow document retrieval; stage presence alone does not locate the bottleneck. (low confidence) [source]
Comparisons and alternatives
- In Phil's 2018 production investigation, an explain execution tree contains SUBPLAN above FETCH, SHARDING_FILTER, and SORT_MERGE, with three IXSCAN children. This is a concrete field example of the stages coexisting, rather than evidence that their presence guarantees a cheap query. (low confidence) [source]
Facts and statements
- In r8.0.0, canUseSubplanning rejects queries with hint, min, max, or a tailable cursor; it requires a nonempty OR at the canonical expression root. Merely containing a nested $or does not satisfy that test. (low confidence) [source]
- SORT_MERGE merges already ordered index-scan streams to provide the requested sort order. It does not mean a blocking SORT occurred. An indexed $or can use separate indexes per clause to support one sort, provided the selected inputs supply compatible ordering. (medium confidence) [source]
- SORT_MERGE alone does not establish that rooted-OR branch subplanning occurred: the documented engineering example also obtains SORT_MERGE from a small $in list by exploding equality intervals into ordered IXSCANs. Inspect the surrounding plan and canonical predicate. (medium confidence) [source]
- SERVER-18777 documents a branch-cache exception: SubplanStage can reuse an $or branch without CachedPlanStage's trial, allowing a poor branch plan to persist without the usual eviction and replanning. SERVER-91095's 2024 description likewise says SBE subplanning branch entries carry neither works nor reads. These records describe that path, not every execution of every OR-shaped query. (medium confidence) (verified as of 2024-10-15) [source]
- Replanning and choosing a subplanner are separate decisions. SERVER-120492 describes restarting planning while explicitly excluding subplanning; SERVER-121973 reports a regression where that exclusion was lost and tassert 8746609 fired after data growth. The latter ticket lists fixes in 8.3.0-rc2 and 9.0.0-rc0. (medium confidence) (verified as of 2026-09-20) [source]
- A forum user's statement that $or always uses SORT_MERGE should not be generalized. MongoDB's index-sort documentation makes ordered index support conditional, and the historical scan-direction bug demonstrates that an OR plan can instead require a blocking sort. (medium confidence) [source]
- In MongoDB r8.0.0, SubplanStage plans the clauses of a rooted $or independently, ranks branch candidates with MultiPlanStage, and builds a composite solution from the winning branches. Rooted refers to the canonical match-expression root being OR. (medium confidence) [source]
- The r8.0.0 SubplanStage caching strategy operates per clause: it can recover branch index tags from an active classic-cache entry and reuse branch shapes across queries. Its documented successful-subplanning strategy does not read or write a cached composite rooted-$or plan. (medium confidence) [source]
- SERVER-121973 records tassert 8746609 after a cached rooted-$or query replans following document growth: the replanning path unexpectedly created a SubPlanner. The issue lists fixes in 8.3.0-rc2 and 9.0.0-rc0. This is a specific regression, not proof that every rooted-$or query replans. (low confidence) (verified as of 2026-09-20) [source]
Children
- Branch-local plan-cache reuse (frontier)
- Subplanner whole-query fallback (frontier)
- Ordered IXSCAN merging (frontier)
- Subplanner explain visibility (frontier)
- Canonical rooted $or eligibility (frontier)
- Per-branch cache reuse and whole-query fallback (frontier)
- Index-provided ordering and merge-sort composition (frontier)
Frontier under this node: Branch-local plan-cache reuse, Canonical rooted $or eligibility, Index-provided ordering and merge-sort composition, Ordered IXSCAN merging, Per-branch cache reuse and whole-query fallback, Subplanner explain visibility, Subplanner whole-query fallback