Multikey index coverage and multiKeyPaths
Parent: MongoDB Query Shape Optimization: Sort, Pagination, Operators and Sharded Targeting · Published reference · snapshot 2026-10-24
↓ Facts as markdownall context files
Source-anchored research for Multikey index coverage and multiKeyPaths. 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
- For an index {genres:1,title:1} where genres is an array and title is scalar, find({genres:"Drama"},{_id:0,title:1}) is a covered-query pattern; including genres in the returned projection prevents coverage. (high confidence) [source]
Structure and components
- A compound multikey index can cover a query that filters an array and returns indexed non-array fields, provided the array is excluded from the result, the query contains no $elemMatch, and the other covered-query requirements hold. (medium confidence) [source]
- multiKeyPaths identifies array-bearing components separately for each indexed field. For example, {field1:[],field2:["field2"]} marks only field2 as multikey even though isMultiKey is true for the compound index. This makes field-level inspection more informative than the index-wide flag alone. (medium confidence) [source]
- An indexed dotted field can have multikey ancestry even if its terminal value is scalar. The worked example records obj.sub2:['obj'] when obj becomes an array, while obj.sub1 records both obj and obj.sub1 when both levels contain arrays. (low confidence) [source]
- A multikey index stores separate entries for distinct array values, with multiple entries potentially pointing to one document; MongoDB automatically makes an index multikey when indexed data contains arrays. (high confidence) [source]
How it works
- An array ancestor can make a dotted indexed field multikey even when its leaf values are scalar. In Pachot's worked example, an array at obj produces multiKeyPaths["obj.sub2"]=["obj"]. Inspect ancestors as well as the projected leaf. (low confidence) [source]
- Coverage requires all predicate and returned fields to be available from the same index; exclude _id explicitly when it is not indexed. Satisfying these field requirements alone does not remove the additional multikey restrictions. (high confidence) [source]
How-to and procedures
- To verify coverage in a classic explain plan, check for an index projection without FETCH and totalDocsExamined:0. An IXSCAN alone proves index access, not index-only execution; the worked coverage tutorial shows both IXSCAN-plus-FETCH and PROJECTION_COVERED plans. (low confidence) [source]
- Use explain("executionStats") to verify document avoidance. For the documented IXSCAN/FETCH plan representation, a covered scan has no FETCH ancestor of IXSCAN and examines zero collection documents. Check the plan as well as totalDocsExamined, especially for empty results. (high confidence) [source]
Problems, failure modes and limitations
- SERVER-130777 reports a resumable-build failure in which an array-producing side write arrives after the collection scan, a clean restart loses its in-memory multikey state, and the committed index is incorrectly flagged non-multikey. The report describes missed results and covered projections returning an array element. Treat this as a reported failure mode, not a verified claim that every deployment of the listed versions is affected. (low confidence) (verified as of 2026-10-12) [source]
Comparisons and alternatives
- One engineering tutorial's text says that projecting phoneNumber from a scalar document remains covered after another document makes phoneNumber multikey, but its own printed explain output shows PROJECTION_SIMPLE, FETCH and totalDocsExamined:1. That example supports checking the plan rather than trusting its heading. (low confidence) [source]
- The historical MongoDB 3.6.0 reproduction reports isMultiKey:false and multiKeyPaths containing an empty array for address.zipcode. Its hinted plan uses IXSCAN and reports totalDocsExamined:0. This illustrates plan selection versus coverage capability, rather than a multikey coverage failure. (low confidence) [source]
Facts and statements
- The multikey manual excludes queries containing $elemMatch from coverage. A historical Stack Overflow answer explains that index keys alone may not distinguish an array of subdocuments from a scalar embedded document with the same indexed values; the question prints a FETCH stage for its $elemMatch reproduction. (medium confidence) [source]
- Multikey indexing can create multiple index entries per document. Percona's original experiment used a 100-element indexed array and described 100 index operations per insertion; this illustrates fan-out, not a transferable performance ratio for current MongoDB. (medium confidence) [source]
- For exact equality to an entire array, a multikey index can narrow candidates using the first query-array element, but MongoDB must fetch candidates to verify the complete array; indexing all named fields does not make this predicate covered. (low confidence) [source]
- Index coverage does not independently guarantee an index-provided array sort. The manual requires full [MinKey,MaxKey] bounds for all sort fields and no multikey-bound path prefix shared with the sort pattern to avoid an in-memory sort. (low confidence) [source]
- The validate method documents inconsistencies where an index has multikeyPaths for non-multikey fields, lacks paths for multikey documents in an older index, or is marked multikey without multikey fields. Metadata consistency is therefore a distinct diagnostic concern. (low confidence) [source]
- Multikey does not categorically prohibit coverage. A compound multikey index can cover eligible queries that return indexed non-array fields, provided path-level multikey information is available and the other covered-query requirements hold. (high confidence) [source]
- The documented multikey coverage conditions exclude queries containing $elemMatch. Adding $elemMatch to improve array-element matching does not establish index-only execution. (low confidence) [source]
Children
- Array ancestry in dotted index paths (frontier)
- Multikey metadata consistency during index builds (frontier)
- Whole-array equality verification (frontier)
- Covered-plan validation with explain (frontier)
Frontier under this node: Array ancestry in dotted index paths, Covered-plan validation with explain, Multikey metadata consistency during index builds, Whole-array equality verification