# Rooted $or subplanning and SORT_MERGE > Source-anchored research for Rooted $or subplanning and SORT_MERGE. Run status: COMPLETED. Parent: [SORT_MERGE and $in sort expansion](https://llms-explorer.com/tree/sort-merge-and-in-sort-expansion/) · 6 facets · 20 facts · page: https://llms-explorer.com/tree/rooted-or-subplanning-and-sort-merge/ ## 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](https://jira.mongodb.org/browse/SERVER-19350) ## 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](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes/) ## 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](https://github.com/mongodb/mongo/blob/r8.0.0/src/mongo/db/exec/subplan.cpp) - 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](https://github.com/mongodb/mongo/blob/r8.0.0/src/mongo/db/exec/subplan.cpp) - 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](https://aws.amazon.com/ko/blogs/tech/documentdb-index-optimization-query-performance/) - 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](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes/) - 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](https://jira.mongodb.org/browse/SERVER-91095) ## 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](https://jira.mongodb.org/browse/SERVER-24027) - 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](https://github.com/mongodb/mongo/blob/r8.0.0/src/mongo/db/exec/subplan.cpp) - 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](https://database-investigator.com/2018/10/15/my-first-mongodb-performance-investigation/) ## 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](https://database-investigator.com/2018/10/15/my-first-mongodb-performance-investigation/) ## 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](https://github.com/mongodb/mongo/blob/r8.0.0/src/mongo/db/exec/subplan.cpp) - 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](https://www.mongodb.com/docs/manual/tutorial/sort-results-with-indexes/) - 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](https://dev.to/mongodb/or-expansion-in-mongodb-and-how-in-is-treated-as-a-range-with-skip-scan-or-an-equality-for-2b38) - 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](https://jira.mongodb.org/browse/SERVER-18777) - 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](https://jira.mongodb.org/browse/SERVER-120492) - 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](https://www.mongodb.com/community/forums/t/when-in-can-use-sort-merge/201141) - 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](https://github.com/mongodb/mongo/blob/r8.0.0/src/mongo/db/exec/subplan.cpp) - 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](https://github.com/mongodb/mongo/blob/r8.0.0/src/mongo/db/exec/subplan.cpp) - 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](https://jira.mongodb.org/browse/SERVER-121973)