Plan cache key and $in length (classic vs SBE)
Parent: SORT_MERGE and $in sort expansion · Published reference · snapshot 2026-10-31
↓ Facts as markdownall context files
Source-anchored research for Plan cache key and $in length (classic vs SBE). 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
- SERVER-83712 lists 7.0.12, 7.3.3 and 8.0.0-rc1 among fixes removing the SBE $in-length component when there is no sort. The r7.0.12 encoder contains the corresponding _hasSort condition. (medium confidence) (verified as of 2026-03-17) [source]
- SERVER-83777 lists 7.0.5, 7.2.1 and 7.3.0-rc0 as fixes capping the $in-length key component at internalQueryMaxScansToExplode + 1. (low confidence) (verified as of 2024-01-02) [source]
How it works
- In the r7.0.12 implementation, classic encodeKeyForMatch records the $in match type and path, plus regex distinctions when present; it does not append the ordinary equality-list cardinality. The SBE cache encoder has a separate cardinality rule. This comparison concerns those encoding paths, not a guarantee that every syntactic $in variant has the same final key. (low confidence) [source]
- The SBE cache cardinality discriminator exists because explode-for-sort can produce one merge branch per unique $in equality value. Different list lengths can therefore require different cached executable plan structures; multiple independently varying $in lists can multiply cache variants. (high confidence) [source]
- In r7.0.12, the SBE encoder appends min(maxScansToExplode + 1, number of unique equality values) only when the $in has an input parameter and the canonical find query has a sort. Above that cap, cardinalities share this discriminator; without a sort, this discriminator is omitted. These are necessary scopes, not a promise of identical complete keys. (high confidence) [source]
- Percona demonstrates inspecting getPlanCache().list() and profiler fields including planCacheKey, queryFramework and fromPlanCache. Recording these together is useful for checking actual cache reuse when comparing $in inputs. (low confidence) (verified as of 2026-04-13) [source]
- SERVER-82548 reports a sorted large-$in query choosing an unwanted index after a 6.0.11-to-7.0.2 upgrade, but MongoDB closed it as a duplicate of SERVER-20616, a plan-ranking sampling issue. A large-$in index-choice regression alone does not establish cache-key fragmentation as its cause. (low confidence) (verified as of 2023-11-30) [source]
Problems, failure modes and limitations
- To validate actual reuse while varying $in lengths, execute ordinary queries and inspect cache statistics or logs. The manual states that explain bypasses existing cache entries and prevents new ones, so explain alone cannot demonstrate a cache hit. (low confidence) [source]
Comparisons and alternatives
- MongoDB's SERVER-89436 describes classic caching as broadly parameterized index-choice information and SBE caching as a more conservatively parameterized executable plan. Its array-equality example b:[1] versus b:[2] shares a classic key but has distinct SBE keys; that example is not a scalar $in list. (low confidence) (verified as of 2025-04-11) [source]
Facts and statements
- SERVER-90415 changed 8.0 and master release builds to use the classic cache for SBE-executed queries, retaining the SBE cache under the SBE-full feature flag. Therefore queryFramework:'sbe' alone does not identify the cache implementation. (high confidence) (verified as of 2024-11-04) [source]
- Mydbops reports a MongoDB 7.0.14 production incident involving $in lists of embedded documents, changing hashes, and 113,702 cache entries. It reports stable memory after forcing classic execution or upgrading to 8.0. This is one workload's observation, not proof that scalar $in length always changes keys. (low confidence) (verified as of 2025-12-08) [source]
- Percona's worked scalar-equality example prints the same planCacheKey after customerId changes from 1234 to 4000. This illustrates value-insensitive keying in that example; it does not establish $in cardinality behavior across engines. (low confidence) (verified as of 2026-04-13) [source]
- MongoDB distinguishes planCacheShapeHash, derived from the plan-cache shape, from planCacheKey, which also reflects usable indexes. For cardinality comparisons, keep indexes and index eligibility controlled; a shared broad shape does not guarantee identical keys. (high confidence) [source]
- SERVER-83777 lists the cardinality-cap fix in 7.0.5, 7.2.1 and 7.3.0-rc0. SERVER-83712 separately lists the no-sort improvement in 7.0.12, 7.3.3, 8.0.0-rc1 and 8.1.0-rc0. A blanket claim that every MongoDB 7.0 SBE $in length always creates a new key overlooks these patch-level changes. (high confidence) (verified as of 2026-09-21) [source]
Children
- SBE $in parameterization eligibility (frontier)
- Explode-for-sort cardinality discrimination (frontier)
- Plan cache implementation versus execution framework (frontier)
- SBE parameterization of object-valued $in predicates (frontier)
- Explode-for-sort cardinality and unique equality values (frontier)
Frontier under this node: Explode-for-sort cardinality and unique equality values, Explode-for-sort cardinality discrimination, Plan cache implementation versus execution framework, SBE $in parameterization eligibility, SBE parameterization of object-valued $in predicates