SBE $lookup pushdown and hash join
Parent: MongoDB Advanced Explain: Aggregation, Sharded Plans, Loose Index Scans and the 8.3 Cost-Based Ranker · Published reference · snapshot 2026-10-24 · skill mongodb-cohort-468120abc19f
↓ Facts as markdownall context files
Source-anchored research for SBE $lookup pushdown and hash join. 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
- EQ_LOOKUP means equality lookup under SBE; it does not by itself mean hash join. Inspect strategy: the demonstrations show HashJoin, IndexedLoopJoin, and NestedLoopJoin as distinct implementations. (high confidence) [source]
Structure and components
- MongoDB documents SBE execution of eligible $lookup stages starting in 6.0. All preceding stages must be SBE-compatible; a foreign pipeline, numeric localField/foreignField path components, or a from collection that is a view or sharded collection excludes the documented path. These are SBE eligibility limits, not statements that $lookup itself rejects those shapes. (high confidence) (verified as of 2026-10-24) [source]
How it works
- Adding a foreign projection pipeline can trade away SBE eligibility even when it enables index coverage. Pachot's example reports zero foreign documents examined with a covering index but slower execution after adding that pipeline; the official eligibility rule explains why a pipeline rewrite must be checked with explain. (high confidence) (verified as of 2025-12-03) [source]
- SERVER-136675 reports a duplicate-array-key regression on master d0787dfc0ca, a 2026-10-08 development build: repeated local key values cause repeated hash probes and sorting of accumulated matches. The ticket reports unaffected comparison runs on 9.0.2, 8.3.11, and 8.0.32, explicitly scopes the regression to master, and is resolved Duplicate; it does not identify a released fix. (low confidence) (verified as of 2026-10-08) [source]
Measurements and reference values
- For the MongoDB 8.0 path described by Pachot, hash-join selection requires allowed disk use, no compatible foreign-field index, and a sufficiently small foreign collection. The described limits are 10,000 documents and 100 MB each for data and storage; treat these internal knobs as version-specific. (low confidence) (verified as of 2025-12-03) [source]
- Pachot's MongoDB 8.0 demonstration describes hash selection when no compatible foreign index exists, effective disk use is allowed, and foreign collection size falls below internal limits. It lists defaults of 10,000 documents and 100 MB each for data size and storage size; treat these as version-specific internal heuristics, not a public tuning contract or evidence of cost-based join enumeration. (low confidence) (verified as of 2025-12-03) [source]
- In Pachot's workload with 260,026 local users and four foreign profiles, HashJoin examines 260,030 documents with no keys, while unindexed NestedLoopJoin examines 1,300,130 documents. The corresponding recorded times are 750 ms and 1,618 ms. These are observations from this dataset, not transferable speedup guarantees. (low confidence) (verified as of 2025-12-03) [source]
Problems, failure modes and limitations
- For pushed-down $lookup, queryPlanner is at explain.queryPlanner when the whole relevant suffix is SBE-compatible; if later stages cannot run in SBE, look under explain.stages[0].$cursor.queryPlanner. The vendor demonstration extracts winningPlan.queryPlan and strategy from this nested location. (high confidence) [source]
- The independent Earezki guide presents a Java benchmark comparing lookup-plus-unwind with embedded metadata, but its shown explain fragment lacks EQ_LOOKUP and strategy. Its latency results therefore do not establish which join algorithm ran and cannot substantiate a claim that SBE hash joins have that cost. (medium confidence) [source]
Facts and statements
- A hash lookup builds from the foreign collection and probes with local keys. DbSchema independently reports an 8.2 example switching from HashJoin to IndexedLoopJoin after adding the foreign-field index. (medium confidence) (verified as of 2026-10-24) [source]
- The February 2023 SERVER-74293 report describes HashLookupStage spilling with possible repeated deserialization. It says small-foreign-collection heuristics made spilling unlikely at that time; it distinguishes this stage from regular HashJoinStage. This is historical design evidence, not a statement of current spill support. (low confidence) [source]
- In the demonstrated hash-join execution, MongoDB scans the foreign collection once to build a hash table and probes it while reading local documents. Both the vendor example and the independent DbSchema example demonstrate HashJoin without an index on the foreign join field. (high confidence) [source]
- An index on foreignField can change an eligible SBE equality lookup from HashJoin to IndexedLoopJoin. DbSchema reports that transition on MongoDB 8.2; Pachot independently demonstrates the same choice in his vendor-published experiment. The transition does not prove the indexed strategy is faster for every workload. (high confidence) (verified as of 2026-09-11) [source]
- SERVER-74293 describes HashLookupStage spilling as possible but unlikely under the small-foreign-collection heuristics used at the time. It also distinguishes HashLookupStage from the regular SBE HashJoinStage. The ticket was created February 22 and updated March 14, 2023; this historical evidence does not establish current spilling support for all join operators. (low confidence) (verified as of 2023-03-14) [source]
Children
- No children recorded.