Reading indexed `$or` plans with explain
Parent: Compound index vs index union for $or · Published reference · snapshot 2026-10-31
↓ Facts as markdownall context files
Source-anchored research for Reading indexed `$or` plans with explain. 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
- An indexed query is not necessarily covered. Coverage means satisfying filtering and returned fields from index keys without fetching documents; a covered read has zero documents examined. (medium confidence) [source]
How it works
- A 2023 Percona forum reporter observed a large $or latency change when adding recurrence to the projection despite apparently equal plans and the same two branch indexes. The thread does not establish the root cause; compare the exact projected operations and execution statistics before attributing such a difference to index choice. (low confidence) [source]
- FETCH above IXSCAN can retrieve requested documents even when the index resolves the filter. A filter attached to FETCH identifies additional document-level predicate evaluation; FETCH alone does not prove residual filtering. (medium confidence) [source]
How-to and procedures
- Run find(filter).explain("executionStats") to examine the winning plan and its execution counters. Compare totalKeysExamined and totalDocsExamined with nReturned when assessing the work done for matching results. (medium confidence) [source]
Problems, failure modes and limitations
- For sorted OR queries, distinguish merging ordered scans with SORT_MERGE from a separate SORT. SERVER-24027 illustrates that branch scan directions must provide compatible ordering for the merge; its reported scan-direction defect concerns MongoDB 3.2.3, not a demonstrated current limitation. (medium confidence) [source]
Comparisons and alternatives
- IXSCAN establishes index use, not complete filter coverage or efficiency. FETCH reads collection documents and may apply residual filtering; compare totalKeysExamined and totalDocsExamined with nReturned instead of approving a plan from its stage names. (medium confidence) [source]
- Sorted indexed $or branches can use SORT_MERGE rather than an OR node followed by a blocking SORT. MongoDB's historical SERVER-51120 diagnosis explicitly identifies SORT_MERGE with IXSCAN children for such queries. (medium confidence) [source]
- For contained $or queries, common predicates can be distributed into branches. Indraneil Seal illustrates category AND (price OR quantity) and competing category/price and category/quantity indexes; inspect the chosen tree rather than inferring access paths from query syntax. (medium confidence) [source]
Facts and statements
- An OR stage with multiple IXSCAN children in inputStages identifies indexed branch union. Read the whole tree: an enclosing FETCH or SUBPLAN can place OR below the root. (high confidence) [source]
- Compound indexes and union can coexist: Moratilla's example merges scans of owner_id/start_date and player_id/start_date indexes. Inspect each branch's indexName and indexBounds to determine which fields constrain that scan. (medium confidence) [source]
- The documented classic plan tree is directly under winningPlan; the slot-based engine exposes the logical tree under winningPlan.queryPlan and also emits slotBasedPlan. A reader must account for this structural difference. (low confidence) [source]
- executionStats describes completed winning-plan execution. allPlansExecution adds partial execution captured during candidate selection; those trial figures are not full executions of every rejected alternative. (low confidence) [source]
- In the documented indexed-OR tree, an OR stage has multiple IXSCAN children in inputStages. Moratilla’s example shows FETCH above OR with separate owner_id/start_date and player_id/start_date compound-index scans; compound indexes and branch union can coexist. (medium confidence) [source]
- Inspect every scan’s indexName, keyPattern, indexBounds and direction. These identify the index and scanned ranges; the presence of an IXSCAN alone does not show that every predicate constrained the scan. (medium confidence) [source]
- totalDocsExamined counts examinations, including filtered-out documents and repeated examinations of the same document; it is neither the result count nor necessarily a unique-document count. (low confidence) [source]
- Read winningPlan for the selected tree and rejectedPlans for alternatives. allPlansExecution adds partial trial statistics, which must not be treated as complete executions of every rejected candidate. (medium confidence) [source]
- Two IXSCAN children do not by themselves identify an OR union. SERVER-106829 records identical scans beneath AND_SORTED in a rejected candidate, illustrating why both the parent stage and candidate location matter. (medium confidence) [source]
- In Moratilla’s indexed-OR example, start_date has an unbounded descending interval while owner_id or player_id has an equality interval. The compound-index name therefore does not imply that start_date narrows the filter. (low confidence) [source]
Children
- OR and SORT_MERGE stage trees (frontier)
- Branch indexBounds and residual FETCH filters (frontier)
- Winning execution statistics versus candidate trial statistics (frontier)
Frontier under this node: Branch indexBounds and residual FETCH filters, OR and SORT_MERGE stage trees, Winning execution statistics versus candidate trial statistics