MongoDB: broad :missing=true on missing_params metadata scans every index key and runs a $lookup per match (slower than the value route) #2233

Summary

On MongoDB, a :missing=true search answered from missing_params metadata (#1984) is much slower than the value-index route when many resources match. The cause is the shape of the query, not the metadata. The metadata route should never be slower than the route it replaces.

Current behavior

Measured on MongoDB 8.0, single-node replica set, 1 GB WiredTiger cache, 300k Synthea resources (121,890 Observations), on main 5d92c68a5. The values are the median of 7 warm runs, page with an accurate total. "Off" means HFS_MONGODB_MISSING_METADATA=false from #2209, which uses the value-index route.

Counts (_summary=count) behave the same. Totals are identical on both routes. #2206 does not change these numbers, because none of these queries has a positive filter to drive from.

Evidence

The count for specimen:missing=true, from the mongod slow-query log:

planSummary: IXSCAN { tenant_id: 1, resource_type: 1, is_deleted: 1, missing_params: 1, last_updated: 1, id: 1 }
keysExamined: 3014906  docsExamined: 121890  nreturned: 1  durationMillis: 9115
pipeline: [ {$match: {$and: [ {tenant_id, resource_type, is_deleted: false},
                               {$or: [ {missing_params: "specimen"}, {missing_params: {$exists: false}} ]} ]}},
            {$lookup: {from: "search_index", let: {candidate_id: {$cond: [...missing → $id, else false]}}, ...}},
            {$match: {"_hfs_missing_present.0": {$exists: false}}}, {$count: "total"} ]
hint: idx_resources_missing_params

There are two separate costs:

1. Unbounded index scan, about 1.0–1.3 s. missing_resource_condition (crates/persistence/src/backends/mongodb/search_impl.rs:658) produces an $or that is nested under the scope $and. With the hint, the planner builds a single IXSCAN with missing_params: [MinKey, MaxKey] and filters in FETCH. That reads every key of the type (3.0M keys for 121,890 Observations, about 25 per document) and fetches every match.
2. Per-document $lookup, about 8 s. missing_fallback_stages (search_impl.rs:666) runs its $lookup for every candidate. That includes documents that have an array, where candidate_id is false and the subpipeline can never match. It is still one subquery per document.

The same predicates in mongosh against that database:

countDocuments({...scope, missing_params: "specimen"})                       58 ms   (covered)
$and:[scope, {$or:[{missing_params:"specimen"},{missing_params:{$exists:false}}]}]   1292 ms (3.0M keys)
$or:[{...scope, missing_params:"specimen"}, {...scope, missing_params:{$exists:false}}]  91 ms (121,890 keys)

Expected behavior

The cost of a metadata-route :missing=true search or count scales with the number of matches, not with the number of index keys of the type. It should be at least as fast as the value-index route for broad matches, and keep its current advantage when few resources match (2 ms vs 3 s).

Suggested approach

• Root the $or: copy the scope (tenant_id, resource_type, is_deleted) into each branch, so {missing_params: X} and {missing_params: {$exists: false}} each get tight bounds. missing_metadata_hint (search_impl.rs:638) already accepts an $or whose branches all reference missing_params.
• Only send documents without an array to the $lookup fallback. For example, split the pipeline (or $facet/$unionWith) so the missing_params: X branch skips the lookup entirely. Or, when the freshness probe (mongodb: detect SearchParameter definition changes and invalidate stale missing_params automatically #1999) already shows that every live document of the type is stamped, drop the $exists: false branch and the lookup.
• Add a plan-shape test asserting keysExamined is close to the match count for a broad absence. Use create_backend_with_stable_probes.

Scope / out of scope

• In scope: the resource-side :missing=true filter and fallback stages for searches and counts.
• Out of scope: the write and storage cost of missing_params (mongodb: measure missing_params write, storage and index-churn cost at scale #2006 / perf(mongodb): measure missing_params cost; HFS_MONGODB_MISSING_METADATA opt-out (#2006) #2209) and the positive-filter driver (mongodb search: benchmark and fix plans for selective positive filters combined with metadata :missing=true #2004 / perf(mongodb): drive missing-metadata counts from a selective positive filter #2206).