# SORT_MERGE and $in sort expansion > Source-anchored research for SORT_MERGE and $in sort expansion. Run status: COMPLETED. 7 facets · 56 facts · page: https://llms-explorer.com/tree/sort-merge-and-in-sort-expansion/ ## Definitions - SORT_MERGE is a non-blocking planner stage that merges the outputs of N child index scans, each already returning keys in the requested sort order. The planner builds it in two cases: a rooted $or whose clauses each use an index that provides the sort, and 'explode for sort', which rewrites one index scan with point-interval bounds (equality or $in) into one IXSCAN per point and merges them. (high confidence) — [source](https://raw.githubusercontent.com/mongodb/mongo/master/src/mongo/db/query/planner_analysis.cpp) - 'Explode for sort' is the planner rewrite that produces SORT_MERGE for sorted $in queries. If the leading fields of an index scan have point bounds (equality or $in) and the next index fields supply the requested sort, the planner replaces the one multi-interval IXSCAN with one IXSCAN per point combination and joins them with a merge-sort stage. For find({a:{$in:[1,2]}}).sort({b:1}) on index {a:1,b:1}, the planner builds the scans a:[1,1] and a:[2,2], each with b:[MinKey,MaxKey]. Each scan returns documents in b order, so the merge keeps that order and no blocking SORT is needed. (high confidence) — [source](https://github.com/mongodb/mongo/blob/35647d07bc4bc11966c90fe7100eb1db0b0ac1d9/src/mongo/db/query/plan_enumerator/README.md) ## Structure and components - The maxScansToExplodeReached explain field arrived with SERVER-48625, 'Add query knobs to explain output and logs', committed on 2020-12-01. The r5.0.0 tag contains that commit (checked with a GitHub compare). The current manual and the server's explain README both list the field under queryPlanner. (medium confidence) — [source](https://github.com/mongodb/mongo/commit/a9982732333fdc6fb853aa8d8596a85cd008f125) - The multiplanner scores each candidate as 1 + advanced/works + eofBonus + tieBreakers. The noSortBonus tie-breaker is at most 1e-4 and is withheld only from plans that contain SORT_DEFAULT or SORT_SIMPLE, so a SORT_MERGE plan keeps it. The bonus only breaks ties. A competing plan that is more productive in the trial period can still beat a SORT_MERGE plan. (high confidence) (verified as of 2026-10-20) — [source](https://github.com/mongodb/mongo/blob/35647d07bc4bc11966c90fe7100eb1db0b0ac1d9/src/mongo/db/query/plan_ranker.h) ## How it works - Several $in lists multiply: two short $in lists on adjacent leading index fields can exceed 200 scans although each list is short, and SERVER-83712 notes that a query with several $in lists may lead to exponential growth. The docs' '201 elements' rule is exact only for a single $in on one leading field. (high confidence) — [source](https://raw.githubusercontent.com/mongodb/mongo/master/src/mongo/db/query/planner_analysis.cpp) - A descending sort does not need a descending index: when the requested sort equals the exact reverse of the sort the index remainder provides, explodeForSort reverses every exploded scan (fixed for $in sorts in 2.6.1). A mixed-direction sort that matches neither the key pattern nor its reverse still ends in a blocking SORT. (high confidence) — [source](https://raw.githubusercontent.com/mongodb/mongo/master/src/mongo/db/query/planner_analysis.cpp) - Index design: put the equality or small-$in fields first and the sort field right after them, for example {a:1,b:1} for find({a:{$in:[...]}}).sort({b:1}). The docs say to place $in early when its arrays are usually small, and where a range predicate would go when arrays are usually large. A sort on a non-prefix subset of an index works only when the query has equality conditions on all preceding keys. (high confidence) (verified as of 2026-10-18) — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-guideline/) - For a rooted $or whose clauses can all use the same index, hint() on that index can force a SORT_MERGE plan when the planner chose a blocking SORT; the ESR page also recommends cursor.hint() when testing candidate indexes. Index filters are an alternative, but they do not persist across restarts and must be set on every node. (medium confidence) — [source](https://jira.mongodb.org/browse/SERVER-19114) - SORT_MERGE competes as a candidate plan and is not guaranteed to win. The classic scorer ranks plans by trial-period productivity and adds only a tie-breaker of at most 1e-4 (kBonusEpsilon) to plans without a blocking sort, so a more productive plan that ends in SORT, for example one on a more selective index, can still win. The query-plans page says the classic winner is the plan that returns the most results in the trial for the least work. (medium confidence) (verified as of 2026-10-20) — [source](https://raw.githubusercontent.com/mongodb/mongo/master/src/mongo/db/query/plan_ranker.h) - Two common over-simplifications mislead index design: 'a $in is in fact a range operator when it gets multiple values' (MongoDB Community Forums, 2022) and '$in is equality without a sort, otherwise a range operator' (Studio 3T blog, 2026-05-06). With a sort, a $in that needs at most 200 scans is exploded and behaves like equality via SORT_MERGE; it behaves like a range only above the cap. (medium confidence) — [source](https://www.mongodb.com/community/forums/t/when-in-can-use-sort-merge/201141) - SBE plan cache and $in length: an exploded SBE plan has one branch per $in element, so the plan cache key encodes the $in length for queries with a sort. SERVER-83777 (7.0.5, 7.2.1, 7.3.0-rc0) caps the encoded length at internalQueryMaxScansToExplode + 1. SERVER-83712 (7.0.12, 7.3.3, 8.0.0-rc1, 8.1.0-rc0) stops encoding it when the query has no sort. SERVER-88892 (open) proposes runtime explosion so one cached plan fits every list length. Parameterized plans are explodable only if the interval tree is a constant or an $eq/$in node, which keeps the branch count stable. (high confidence) (verified as of 2026-10-20) — [source](https://jira.mongodb.org/browse/SERVER-83777) - Security: explodeForSort() multiplied point-interval counts into numScans without overflow checks, so a crafted find-with-sort using large $in arrays on a suitable compound index could wrap the counter, bypass the maxScansToExplode check and exhaust memory during planning. An authenticated user with ordinary read/write privileges can trigger it (CVE-2026-82076, SERVER-128253, CWE-190, CVSS v4.0 7.1, v3.1 6.5). SERVER-128253 lists fixes in 7.0.41, 8.0.30, 8.2.13, 8.3.9 and 9.0.0-rc2; the CVE record lists no workaround. Master now guards the multiplication with a size_t maximum. (high confidence) (verified as of 2026-10-20) — [source](https://cveawg.mitre.org/api/cve/CVE-2026-82076) - Dedup on the merge: explodeForSort sets dedup=false when it explodes a single IXSCAN, because distinct point prefixes give disjoint results. It sets dedup=true when several scans sit under an OR, since their bounds may overlap, or when any original scan needs deduplication, such as a multikey index. (low confidence) (verified as of 2026-10-20) — [source](https://raw.githubusercontent.com/mongodb/mongo/master/src/mongo/db/query/planner_analysis.cpp) - Plan-selection context for 8.3 and later: multi-planning with a cost-based ranker (CBR) backup is the default plan selection mechanism for eligible queries, and the docs say CBR is currently invoked for only a small share of queries. The explain docs carry the same note. (medium confidence) (verified as of 2026-10-20) — [source](https://www.mongodb.com/docs/manual/core/query-plans/) - After the point-bound prefix, the requested sort must be a prefix of the remaining index key pattern, or a prefix of its reverse. Each exploded scan then returns documents in the order of those remaining fields, because its prefix is a single point. (high confidence) — [source](https://github.com/mongodb/mongo/blob/35647d07bc4bc11966c90fe7100eb1db0b0ac1d9/src/mongo/db/query/planner_analysis.cpp) - The merge node's dedup flag is true when more than one explodable scan feeds it, because the scans can overlap. It is also true when an original scan needs dedup, as a multikey index does. If a single non-multikey scan is exploded, the per-value scans have disjoint prefixes and the merge skips dedup. SERVER-80256 (fixed in 7.0.1 and 7.1.0-rc0, affected 7.0.0) removed an assumption in explodeForSort that index scans always produce disjoint results. (high confidence) — [source](https://github.com/mongodb/mongo/blob/35647d07bc4bc11966c90fe7100eb1db0b0ac1d9/src/mongo/db/query/planner_analysis.cpp) - Both execution engines can run SORT_MERGE. The classic engine has a merge-sort stage whose header says it merges the outputs of N children, each sorted in the order of a pattern. The SBE stage builder maps STAGE_SORT_MERGE to buildSortMerge, which feeds each child's sort keys into a sorted-merge stage. When dedup is set, SBE adds a unique stage keyed on RecordId: makeUnique for clustered collections, makeUniqueRoaring otherwise. A code comment (TODO SERVER-114436) says this assumes all merged results come from the main collection. (medium confidence) (verified as of 2026-10-20) — [source](https://github.com/mongodb/mongo/blob/35647d07bc4bc11966c90fe7100eb1db0b0ac1d9/src/mongo/db/query/stage_builder/sbe/builder.cpp) - In explain output, a SORT_MERGE stage with a sortPattern and one IXSCAN child per point combination means the sort comes from the index. A SORT stage means a blocking in-memory sort. The manual says that a plan with no explicit SORT stage got its sort order from an index. queryPlanner.maxScansToExplodeReached is true when the scan cap prevented explode-for-sort. MongoDB's own test sets the cap to 0, runs find({e:{$in:[250 values]}}).sort({s:1}) on index {e:1,s:1}, and asserts the flag. (high confidence) — [source](https://www.mongodb.com/docs/manual/reference/explain-results/) - A descending sort over an ascending index still explodes. If the requested sort is not a prefix of the index's provided sort, the planner tries the reverse and flips the exploded scans when it matches. Before SERVER-13618 (fixed in 2.6.1 and 2.7.0), this case produced a blocking SORT. Lim Jong Eun's explain shows three backward IXSCANs under SORT_MERGE for sort({O_ENTRY_D:-1}) on index O_C_ID_1_O_ENTRY_D_1. (high confidence) — [source](https://jira.mongodb.org/browse/SERVER-13618) - SORT_MERGE also serves $or queries. Each $or clause needs an index that returns results in the requested order, and the planner merges the per-clause streams. Example: $or:[{a:1},{b:1}] sorted by c with indexes {a:1,c:1} and {b:1,c:1}. The planner accepts an OR node for explosion only if every child is an IXSCAN or a FETCH over one IXSCAN. FETCH children were allowed starting in 4.7.0 (SERVER-25782). (high confidence) — [source](https://jira.mongodb.org/browse/SERVER-19114) - For a rooted $or with a sort where all clauses can use the same index, hint on that index can force the SORT_MERGE plan when the planner picks a blocking SORT. An index filter does the same, but it does not persist across restarts and must be set on each node. The cause is the subplanner, which picks each clause's best plan alone and can miss a combination whose clauses all provide the sort. SERVER-19114 is an open backlog item. (low confidence) (verified as of 2026-10-18) — [source](https://jira.mongodb.org/browse/SERVER-19114) - If a sort field itself is multikey, the planner still does not use it to provide the sort after explosion. SERVER-45823, 'Allow multikey index scans to be exploded for sorts', has been in Backlog since 2020-01-28 (last updated 2022-12-06). It says: 'Currently the planner prevents exploding an index to provide a sort when the sort is on a multikey field.' (medium confidence) (verified as of 2026-10-20) — [source](https://jira.mongodb.org/browse/SERVER-45823) - For a parameterized $in in a query with a sort, the plan cache key includes the $in length capped at internalQueryMaxScansToExplode + 1, because the number of SORT_MERGE branches equals the number of $in values. All lists above the cap share one cache entry. SERVER-83777 added the cap (fixed in 7.0.5, 7.2.1 and 7.3.0-rc0). SERVER-83712 stopped adding the length when the query has no sort. SERVER-88892 (Open) proposes dropping the exact length from the SBE key by rebuilding the branches from bound parameter values when a plan is recovered. To keep cached plans valid, explosion of a parameterized scan requires each leading point field's bounds to come from an $eq, an $in, or a constant. (high confidence) (verified as of 2026-10-20) — [source](https://github.com/mongodb/mongo/blob/35647d07bc4bc11966c90fe7100eb1db0b0ac1d9/src/mongo/db/query/canonical_query_encoder.cpp) - Raising the cap lets more branches explode, but each branch is another index scan. The source comment says 'Too many ixscans spoil the performance', and Lim Jong Eun warns that raising internalQueryMaxScansToExplode increases query workspace needs and can cause memory pressure at scale. Neither source measures where the cost of many branches overtakes a blocking sort. (low confidence) — [source](https://github.com/mongodb/mongo/blob/35647d07bc4bc11966c90fe7100eb1db0b0ac1d9/src/mongo/db/query/planner_analysis.cpp) ## How-to and procedures - To check a query, run explain('executionStats') and read four things: whether the plan has SORT_MERGE or SORT; whether each IXSCAN under SORT_MERGE has a single point in its leading indexBounds; the per-branch keysExamined and seeks; and queryPlanner.maxScansToExplodeReached. To test the cap, setParameter with internalQueryMaxScansToExplode works at runtime. MongoDB's own test sets it to 0 and then restores 200. (high confidence) — [source](https://www.mongodb.com/docs/manual/reference/explain-results/) ## Problems, failure modes and limitations - QueryPlannerAnalysis::analyzeSort tries three things in order: (1) keep the chosen plan if it already provides the sort, reversing the scan direction if needed; (2) call explodeForSort; (3) only if both fail, add a blocking SORT stage. A failed explode therefore shows in explain as a SORT stage where a SORT_MERGE was expected. (high confidence) — [source](https://raw.githubusercontent.com/mongodb/mongo/master/src/mongo/db/query/planner_analysis.cpp) - MongoDB's ESR guideline: when $in is used with .sort() and has fewer than 201 array elements, MongoDB expands the elements and merges them in index sort order with a SORT_MERGE stage, so $in behaves like an equality predicate. With 201 or more elements $in is ordered like a range operator and later index fields cannot be used for the sort. The page warns the 201-element behavior is not guaranteed to stay the same across versions. It matches the code default: 200 scans pass, 201 do not. (high confidence) (verified as of 2026-10-18) — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-guideline/) - A SORT_MERGE plan lets a limit stop early, while a blocking SORT must read every match before returning any (with a limit it keeps only limit+skip entries). Example: index {O_C_ID:1,O_ENTRY_D:1}, $in of 3 ids with 5,000 documents each, sort by O_ENTRY_D, limit 10 examined 12 keys and 10 documents. SERVER-21628 (a mongos-only plan bug, fixed in 3.3.4) measured 2,000 keys examined with SORT against 11 with SORT_MERGE at limit 10. (medium confidence) — [source](https://medium.com/@zl2-/mongodb-in-or-sort-%EC%BF%BC%EB%A6%AC%EC%99%80-sort-merge-%EB%8B%A8%EA%B3%84-479bf8d53ac6) - In explain, a successful explode shows a SORT_MERGE stage with one IXSCAN input per exploded point, usually under FETCH and LIMIT. A failed one shows a SORT stage, which the explain docs define as an in-memory sort. queryPlanner.maxScansToExplodeReached (boolean) reports that the scan cap blocked the rewrite; SERVER-128253's control case shows it set to True with 200 values per $in array. (high confidence) — [source](https://www.mongodb.com/docs/manual/reference/explain-results/) - internalQueryMaxScansToExplode is an Atomic knob with validator gte 0 and set_at startup and runtime. Its IDL gives the wire name maxScansToExplode and marks it pqs_settable. SERVER-83777 describes it as the limit on the maximum number of explode-for-sort index scans. (medium confidence) (verified as of 2026-10-20) — [source](https://raw.githubusercontent.com/mongodb/mongo/master/src/mongo/db/query/query_optimization_knobs.idl) - QueryPlannerAnalysis::analyzeSort (planner_analysis.cpp:1597-1703) tries three things in order. It first checks whether the plan already provides the sort, reversing scan direction if needed. It then calls explodeForSort. If both fail, it adds a blocking SORT stage, plus a FETCH if the plan does not cover the sort keys. (high confidence) — [source](https://github.com/mongodb/mongo/blob/35647d07bc4bc11966c90fe7100eb1db0b0ac1d9/src/mongo/db/query/planner_analysis.cpp) - The ESR guideline words the limit as 'fewer than 201 array elements' for one $in. The planner enforces it on the total scan count, not on $in length. It multiplies the point counts of all leading point fields and sums the counts of all scans under an $or. By that rule, two $in fields of 15 and 14 values need 210 scans and exceed the cap although neither list reaches 201 elements. The guideline also states that the 201-element behavior 'is not guaranteed to stay the same for all MongoDB versions'. (high confidence) (verified as of 2026-10-20) — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-guideline/) - With a limit, the merge reads only the head of each branch, so it examines few keys. Pachot's test (index {category:1, publishedDate:-1, duration:1}, three $in categories, limit 10) returned 10 documents after examining 12 keys and 10 documents. Lim Jong Eun's test (three $in values on O_C_ID, sort({O_ENTRY_D:-1}).limit(10), 5,000 documents per value) also reports 12 keys and 10 documents. Both are single runs on small test data. (medium confidence) — [source](https://dev.to/franckpachot/equality-with-multiple-values-preserving-sort-for-pagination-ddh) - $ne, $nin, and $regex are range operators under ESR, not equality operators. They do not give the planner an explicit list of point values, so they cannot be exploded into a SORT_MERGE. (medium confidence) — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-guideline/) - To paginate across all values of a low-cardinality leading field without filtering on it, Pachot passes db.coll.distinct(field) as the $in list. On a 1M-document collection with 20 categories and limit 10, this examined 29 keys and 10 documents in 1 ms. It works only while the distinct list stays under the cap (about 200 values). Pachot's contrast case, a (MinKey, MaxKey] bound on the same index, examined 1,006,087 keys and took about 406 ms. (low confidence) — [source](https://dev.to/franckpachot/equality-with-multiple-values-preserving-sort-for-pagination-ddh) - Explode for sort dates from the 2.6 series; the source still calls it 'quick-and-dirty SERVER-1205'. It originally capped explosion at a hard-coded 50 scans. SERVER-14034, fixed in 2.6.4 and 2.7.2, raised the cap to 200 and made it a query knob. SERVER-14034 reports that all production releases since 2.6.0 had the 50-scan limit and lists no workaround. (medium confidence) — [source](https://jira.mongodb.org/browse/SERVER-14034) ## Comparisons and alternatives - Do not count on an index-provided sort when a $in, or the product of several $in lists, needs more than 200 scans: the planner falls back to a blocking in-memory SORT. SERVER-14034 records the original symptom, when the cap was still 50: a 53-element $in chose the _id index instead of {_a:1,_id:1} and ran slowly. The docs advise placing a large-array $in where a range predicate would go. (medium confidence) — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-guideline/) - Collation: if the index collation differs from the query collation and a sort field has string bounds, explodeForSort refuses because the index cannot provide that sort. Historic bug SERVER-51120: SORT_MERGE misread collated index keys and returned the right documents in the wrong order (repro: collated {a:1,b:1} index, $in on a, sort on b); fixed in 4.9.0, 4.4.2, 4.2.11, 4.0.21 and 3.6.21. (high confidence) — [source](https://raw.githubusercontent.com/mongodb/mongo/master/src/mongo/db/query/planner_analysis.cpp) - Multikey indexes limit explosion. If an index is multikey but has no path-level multikey metadata, the planner never explodes it. If a multikey field falls inside the sort-providing suffix, the provided sort ends before that field. Before 8.1.0-rc0, any multikey field after the point prefix, even one after the sort fields, forced a blocking SORT. SERVER-56865 shows a SORT_MERGE plan on {e:1,s:1,r:1} turning into SORT after r became multikey. That ticket closed as Fixed in 8.1.0-rc0 on 2025-01-10, and the current code breaks out of the suffix loop instead of returning false. SERVER-97418 was closed as its duplicate. (high confidence) (verified as of 2026-10-20) — [source](https://github.com/mongodb/mongo/blob/35647d07bc4bc11966c90fe7100eb1db0b0ac1d9/src/mongo/db/query/planner_analysis.cpp) - If the index collation differs from the query collation and a sort field can hold strings, the planner skips explosion. Separately, SERVER-51120 fixed SORT_MERGE returning wrongly ordered but complete results when the index was collated. It affected $or and $in queries and was fixed in 3.6.21, 4.0.21, 4.2.11, 4.4.2 and 4.9.0. (medium confidence) — [source](https://github.com/mongodb/mongo/blob/35647d07bc4bc11966c90fe7100eb1db0b0ac1d9/src/mongo/db/query/planner_analysis.cpp) - Two sources advise treating $in as a range predicate whenever the query sorts. A Mydbops article (2022-11-21) says 'if used with sort, then $in is treated as a range match' and advises index (age, name) instead of (name, age). A MongoDB community-forum answer (2022-11-24) says '$in is in fact a range operator when it gets multiple values'. That index order avoids SORT_MERGE. It conflicts with the ESR guideline and the planner code for lists within the 200-scan cap. (medium confidence) — [source](https://www.mydbops.com/blog/mongodb-rule-for-indexing-series-1) ## Facts and statements - explodeForSort preconditions on master: the plan root (after skipping a SHARDING_FILTER) is a bare IXSCAN, a FETCH over one IXSCAN, or an OR whose children are all of those; for each scan the leading index fields have only point-interval bounds (equality or $in), at least one index field remains after them, and the requested sort is a prefix of the remaining key pattern or of its exact reverse. A simple-range scan or a leading field with non-point bounds stops the rewrite. (high confidence) (verified as of 2026-10-20) — [source](https://raw.githubusercontent.com/mongodb/mongo/master/src/mongo/db/query/planner_analysis.cpp) - The rewrite is allowed only if the total number of generated scans is at most internalQueryMaxScansToExplode (default 200). The count is the product of the point-interval counts across the exploded leading fields of each scan, summed over the $or branches; it is not the $in array length. Above the cap the planner sets hitScanLimit (explain: queryPlanner.maxScansToExplodeReached) and falls back to a blocking SORT. SERVER-14034's example: $in lists of 10 and 6 on index {a:1,b:1,c:1} need 60 scans. (high confidence) (verified as of 2026-10-20) — [source](https://raw.githubusercontent.com/mongodb/mongo/master/src/mongo/db/query/planner_analysis.cpp) - Verification recipe: run explain('executionStats'); (1) look for SORT_MERGE and count its IXSCAN inputs, which should equal the product of the $in sizes; (2) read queryPlanner.maxScansToExplodeReached; (3) compare totalKeysExamined with nReturned; (4) if a SORT stage appears, check for a multikey index, a collation mismatch, a FETCH above an OR, or a scan count above 200 before redesigning the index. (medium confidence) — [source](https://www.mongodb.com/docs/manual/reference/explain-results/) - Multikey indexes: if an index is multikey but has no path-level multikeyPaths metadata, explodeForSort never runs. A multikey field inside the equality/$in prefix still explodes, and the merge then deduplicates. Before the SERVER-56865 fix, a multikey field after the sort fields (index {e:1,s:1,r:1} with r multikey) also forced a blocking SORT. The fix ships in 8.1.0-rc0 and no backport is listed, so 7.0 and 8.0 may still show SORT. (high confidence) (verified as of 2026-10-20) — [source](https://jira.mongodb.org/browse/SERVER-56865) - A FETCH above an OR blocks explode-for-sort. SERVER-96421 (Backlog, unresolved, last updated 2024-12-12) shows SORT over FETCH over OR over 2 IXSCANs where four IXSCANs under a SORT_MERGE were expected. The code accepts only a bare IXSCAN or FETCH-over-IXSCAN as OR children; SERVER-25782 earlier allowed FETCH children. (high confidence) (verified as of 2026-10-20) — [source](https://jira.mongodb.org/browse/SERVER-96421) - Rooted $or with sort: the subplanner picks the best plan for each clause on its own, so it can choose a clause plan that does not provide the sort and end with a blocking SORT even though a SORT_MERGE plan exists. SERVER-19114 is an unresolved Query Optimization backlog item (updated 2025-03-26); its 2020-03-09 status note says all supported versions are affected. (low confidence) (verified as of 2026-10-20) — [source](https://jira.mongodb.org/browse/SERVER-19114) - Lineage: explode-for-sort dates to MongoDB 2.6 (2014). Its cap was a hard-coded 50 scans and became the 200-scan query knob in 2.6.4 (SERVER-14034); reverse-order sorts were fixed in 2.6.1 (SERVER-13618); a bug that combined $or bounds and forced in-memory sorts was fixed in 2.6.2 (SERVER-13754); SORT_MERGE for descending $or clauses arrived in 3.4.2 (SERVER-24027). (high confidence) — [source](https://jira.mongodb.org/browse/SERVER-14034) - Explosion covers only the leading run of index fields whose bounds are all point intervals. It stops at the first field with a range or an empty bound. The planner counts one scan per combination of points, so the count is the product of the point counts of those fields. If every index field has point bounds, no field is left to supply a sort and the planner does not explode. The SERVER-14034 example is an {a:1,b:1,c:1} index with 10 and 6 $in values, which needs 60 scans. (high confidence) — [source](https://github.com/mongodb/mongo/blob/35647d07bc4bc11966c90fe7100eb1db0b0ac1d9/src/mongo/db/query/planner_analysis.cpp) - internalQueryMaxScansToExplode caps explode-for-sort. Its default is 200, it must be an integer of at least 0, and it can be set at startup or at runtime. The planner compares the total scan count with the cap using '>', so 200 scans still explode and 201 do not. Above the cap the planner keeps the blocking SORT. A test in the MongoDB tree builds an $in with exactly internalQueryMaxScansToExplode values and expects explode-for-sort, and an $in one value longer and expects none. (high confidence) (verified as of 2026-10-20) — [source](https://github.com/mongodb/mongo/blob/35647d07bc4bc11966c90fe7100eb1db0b0ac1d9/src/mongo/db/query/query_optimization_knobs.idl) - For index design, a short $in (within the 200-scan cap) behaves like an equality field, so it goes before the sort field. A long $in behaves like a range field, so it goes where a range predicate would go. The ESR guideline says to include $in 'earlier in the index specification' if you typically use small arrays, and 'where you would include a range predicate' if you typically use large arrays. Pachot describes the short-list case as Equality, Equality, Sort. (high confidence) — [source](https://www.mongodb.com/docs/manual/tutorial/equality-sort-range-guideline/) - The planner explodes only three plan shapes, after skipping a SHARDING_FILTER: an IXSCAN, a FETCH over one IXSCAN, or an OR whose children are all IXSCAN or FETCH-over-IXSCAN. A FETCH above an OR does not qualify. SERVER-96421 shows a filter with an $or of $in clauses on a and b plus a residual d:{$exists:false}, sorted by c. The plan falls back to SORT over FETCH over OR over two IXSCANs. SERVER-96421 is in Backlog (last updated 2024-12-12). (high confidence) (verified as of 2026-10-20) — [source](https://github.com/mongodb/mongo/blob/35647d07bc4bc11966c90fe7100eb1db0b0ac1d9/src/mongo/db/query/planner_analysis.cpp) - SERVER-103029 (Backlog, last updated 2026-05-27) reports that the planner does not combine clauses from different $or selectors as a Cartesian product. For an {a:1,b:1,c:1} index and two $or clauses under an $and, sorted by b, the planner builds two SORT_MERGE branches constrained only on a. Rewriting the filter by hand as four $or branches gives four scans that constrain both a and b. (low confidence) (verified as of 2026-10-20) — [source](https://jira.mongodb.org/browse/SERVER-103029) - Reports show $in and $or planning differently for sorted queries. SERVER-21628 (sharded cluster through mongos, fixed in 3.3.4) saw $in plus sort add a SORT stage and examine 2,000 keys. An equivalent $or that used SORT_MERGE examined 11 keys, and the same $in on a replica set examined 11. A 2022 community-forum report on a 100,000-document collection says $or always produced SORT_MERGE but $in did not. That thread has no explain output and no resolution. (medium confidence) — [source](https://jira.mongodb.org/browse/SERVER-21628) - If explosion fails, the plan holds a blocking SORT. Its explain shows memLimit 104857600 (100 MiB) in the SERVER-56865 repro on a 4.5.0 development build. The manual says that since 6.0 a stage needing more than 100 megabytes of memory spills to temporary files unless the query sets allowDiskUse:false. (medium confidence) (verified as of 2026-10-18) — [source](https://jira.mongodb.org/browse/SERVER-56865) - Above the cap the plan keeps its default shape: one IXSCAN whose bounds list every point, for example a:[[1,1],[2,2]] with b:[MinKey,MaxKey]. That scan returns keys ordered by a first, so a sort on b needs a blocking SORT. Pachot's 201-value example shows SORT over PROJECTION_COVERED over one IXSCAN whose r bounds list individual points. (medium confidence) — [source](https://github.com/mongodb/mongo/blob/35647d07bc4bc11966c90fe7100eb1db0b0ac1d9/src/mongo/db/query/plan_enumerator/README.md)