Winning vs Rejected Plans, Candidate Enumeration, and Plan Cache Behavior

Learning objectives

1. AtlasMart problem: two plausible indexes, one query shape

A candidate plan is a physical strategy the planner considers for a query. A winning plan is selected for execution; rejected plans are alternatives not chosen. A plan cache query shape groups queries by predicate/sort/projection structure rather than literal values. Its planCacheShapeHash depends on shape, while planCacheKey also depends on indexes currently available for that shape.

MongoDB 8.3 can use the classic multi-planner and, for eligible queries, a cost-based-ranker backup. If CBR participates, explain may include abstract costEstimate, cardinalityEstimate, and estimation metadata. Those fields are not guaranteed for every query and should never be hard-coded into monitoring parsers.

2. Seed data and create genuinely competing indexes

The two compound indexes contain the same equality fields in opposite order, followed by the same sort field. Both are plausible for the target query, which lets the planner have something real to compare.

const c=db.orders_ch12_l2;c.drop();const base=new Date("2026-08-01T00:00:00Z");let b=[];for (let i=0;i<30000;i++) {  b.push({_id:i,tenantId:`tenant-${i%30}`,status:["paid","packed","shipped"][i%3],createdAt:new Date(base.getTime()+i*30000),totalCents:500+(i%12000)});  if (b.length===1000) { c.insertMany(b); b=[]; }}if (b.length) c.insertMany(b);c.createIndex({tenantId:1,status:1,createdAt:-1},{name:"idx_tenant_status_created"});c.createIndex({status:1,tenantId:1,createdAt:-1},{name:"idx_status_tenant_created"});printjson({count:c.countDocuments({}),indexes:c.getIndexes().map(x=>x.name)});

3. Inspect candidate selection with allPlansExecution

allPlansExecution runs the winning plan to completion and includes partial trial-period statistics for alternate candidates when they exist. It is diagnostic, not a normal request path. Because explain bypasses the cache, this section tells you what candidates were evaluated now—not what a prior application call reused from cache.

const c=db.orders_ch12_l2;const filter={tenantId:"tenant-7",status:"shipped"};const sort={createdAt:-1};const ex=c.find(filter).sort(sort).limit(100).explain("allPlansExecution");printjson({  shape:ex.queryPlanner?.planCacheShapeHash,  key:ex.queryPlanner?.planCacheKey,  winning:ex.queryPlanner?.winningPlan,  rejected:ex.queryPlanner?.rejectedPlans,  allPlansExecution:ex.executionStats?.allPlansExecution});

4. Observe the cache with normal executions

Plan-cache state is created by ordinary eligible query execution, not by explain. The cache can move through Missing → Inactive → Active states; exact transition timing is implementation/runtime-dependent. Not every query gets a cache entry: the optimizer only caches shapes that can have more than one viable plan. PlanCache.list() wraps $planCacheStats.

const c=db.orders_ch12_l2;const pc=c.getPlanCache();pc.clear();const q={tenantId:"tenant-7",status:"shipped"};for (let i=0;i<10;i++) c.find(q).sort({createdAt:-1}).limit(100).toArray();printjson(pc.list([{$project:{version:1,isActive:1,planCacheShapeHash:1,planCacheKey:1,timeOfCreation:1,peakTrackedMemBytes:1,createdFromQuery:1}}]));// DDL invalidates the relevant collection plan cache.c.createIndex({totalCents:1},{name:"idx_disposable_total"});print("after createIndex cache entries:",pc.list().length);c.dropIndex("idx_disposable_total");

5. Deliberately wrong: force a hint because yesterday's plan was bad

A hard-coded hint() can turn a diagnosis into a long-lived constraint that outlasts the data distribution it was meant to fix. Use hints as controlled experiments: compare normal planner behavior with a candidate index while holding dataset/query constant. If a persistent policy is genuinely required, MongoDB 8.0+ query settings are the supported cluster-persistent mechanism; deprecated index filters should not be the default answer.

Production judgment. Cache inspection is a snapshot, not a contract. On replica sets different members can have different cache contents because they serve different read traffic. On sharded clusters $planCacheStats targeting depends on read preference/allHosts behavior. Observe shape hashes, candidate work, actual execution stats, and workload distributions before intervening.

docker rm -f atlasmart-mongo-ch12-l2docker volume rm atlasmart-mongo-ch12-l2-data

Authoritative references

• MongoDB 8.3 release notes — Current stable series, patch-sensitive behavior, 8.3 query-planning/profiling additions.
• Explain command — Verbosity modes and explain behavior.
• Explain results — Plan stages, execution statistics, query-shape hashes, and version-dependent output.
• Query plans — Candidate selection, cost-based ranker backup, plan-cache states, and cache invalidation.
• PlanCache.list() — Current mongosh plan-cache inspection interface.
• $planCacheStats — Plan-cache documents and engine-dependent output.
• Database profiler — Profiler levels, overhead, filters, thresholds, and security considerations.
• $currentOp — Preferred live-operation inspection stage.
• Slow query monitoring — Profiler/currentOp diagnostic workflow.
• $queryStats — Managed-deployment query-shape statistics and stability/availability caveats.
• Query shapes — MongoDB 8.x query-shape and plan-cache-shape terminology.
• Query settings — MongoDB 8.0+ persistent query-shape settings that supersede deprecated index filters.