Multiplanner scoring and tie-breakers
Parent: SORT_MERGE and $in sort expansion · Published reference · snapshot 2026-10-23 · skill mongodb-cohort-71089ad6ce1a-pending
↓ Facts as markdownall context files
Source-anchored research for Multiplanner scoring and tie-breakers. 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
- Trial productivity can mis-rank candidates when an ordered scan's early sample does not represent the full data distribution: SERVER-20616 describes correlated values making a less selective index plan look productive during the trial. A 2024 study of MongoDB 7.0.1 also found test cases where FPTP chose an index scan over a faster collection scan, with some chosen plans over three times slower in that test space. (high confidence) (verified as of 2024-09-26) [source]
- The same scorer has a separate groupByDistinctBonus for qualifying aggregation DISTINCT_SCAN plans, derived from productivity and potentially larger than epsilon. When forceIntersectionPlans is enabled, it adds 3 to matching index-intersection scores, overriding the ordinary no-intersection preference. (medium confidence) (verified as of 2026-10-20) [source]
Comparisons and alternatives
- An IDEALO 2020 report on MongoDB 4.0 describes two candidates with the same 112 works but 101 versus 42 advances; the planner selected the higher-productivity candidate even though its reported trial estimate was slower, 23 ms versus 6 ms. The example shows how a work-based score can diverge from measured time in that shard workload. (low confidence) (verified as of 2020-10-02) [source]
Facts and statements
- The classic multiplanner runs candidates in a bounded round-robin trial and chooses the highest trial score. MongoDB's documented classic formula is base 1 + advanced / works + EOF bonus + tie-break bonuses; works count logical execution steps, not elapsed milliseconds. (high confidence) (verified as of 2026-10-20) [source]
- At MongoDB source revision 35647d07, regular plan-shape bonuses use epsilon = min(1 / (10 * max(advances, 1)), 1e-4): plans without FETCH, blocking SORT_DEFAULT or SORT_SIMPLE, and AND_HASH or AND_SORTED receive the corresponding bonuses. The scorer's no-sort check names only SORT_DEFAULT and SORT_SIMPLE; reading that code implies a SORT_MERGE stage alone does not forfeit the no-sort bonus. (high confidence) (verified as of 2026-10-20) [source]
- At source revision 35647d07, MongoDB stores the final adjusted ranking score separately from QuerySolution::score, which explain displays. Tie-break bonuses can therefore affect winner ordering without appearing in the displayed trial score. (high confidence) (verified as of 2026-10-20) [source]
- Starting in MongoDB 8.3, the documented default for eligible queries is multi-planning with a cost-based ranker backup. After a short trial, MongoDB may continue multiplanning or use CBR cost estimates, so classic FPTP scoring is not the universal selection path. (high confidence) (verified as of 2026-10-23) [source]
- In experiments on MongoDB 7.0.1 with simple two-range `find` queries, University of Sydney researchers found cases where FPTP chose an index scan even though a collection scan ran faster. For queries with an available matching index, they reported that MongoDB 7.0.1 omits COLLSCAN from the FPTP candidate set unless explicitly requested. They also found that MongoDB counts index-entry retrieval plus document fetch as one logical work unit, which understates the index scan's real-time cost in their tests. These findings are limited to the tested version and workloads. (low confidence) (verified as of 2024-09-26) [source]
- Rosen Ivanov's *Information* article, published 15 May 2026 after its 31 March v1 preprint, reports a synthetic MongoDB 8.2.5 aggregation test in which both candidate plans scored 2.0002 and `idx_category` won by its position in the plan array. The v1 preprint was labeled not peer-reviewed. This single reported test case does not estimate how often equal-score ties occur. (low confidence) (verified as of 2026-10-23) [source]
Children
- No children recorded.