Connect data distribution and query shape to keys/docs examined, sort support, and multikey behavior instead of judging indexes by name alone.
Index Selectivity, Cardinality, Sort Support, Multikey Effects, and Query Shape
Use value distribution, sort requirements, multikey expansion, and query shape to explain why the same index family behaves differently across workloads.
Learning objectives
Define selectivity and cardinality from actual value distributions rather than field names.
Relate totalKeysExamined / nReturned and
totalDocsExamined / nReturned to query work.
Show when a compound scalar index supports both filtering and sort order.
Expose multikey expansion and its special sorting constraints.
Use query-shape hashes to distinguish structural similarity from literal selectivity differences.
This lesson pins MongoDB Community Server
8.3.8 with
mongodb/mongodb-community-server:8.3.8-ubuntu2204-slim
and mongosh 2.10.0. The server is a disposable
standalone published only on loopback
127.0.0.1:27074. Authentication and TLS are
disabled only for this isolated local lab. Feature Compatibility
Version (FCV) is observed but never changed. Default read/write
concern and primary read preference apply. Atlas, Search, KMS,
and Enterprise Advanced are not mandatory. The fixture
intentionally has heavy skew: 80% fulfilled, 15% processing, and
5% fraud-review. This lets the same indexed field behave
differently for different predicates. Runtime measurements are
not pre-filled: this generation environment has no
Docker/mongod/mongosh runtime, so learners must record the
values produced on their own machine.
1. Selectivity is a property of the predicate and distribution
Selectivity describes how strongly a predicate
narrows the collection. Cardinality is the
number of distinct values or estimated/result counts relevant to
planning. An index on a low-cardinality field such as
status may still help some rare values while
offering little benefit for a value that matches most of the
collection.
A multikey index is created automatically when
an indexed field contains arrays. One document can contribute
multiple index keys, so totalKeysExamined can
exceed the number of documents examined or returned. That is not
an error; it is a direct consequence of array expansion.
docker rm -f atlasmart-mongo-ch12-l3 2>/dev/null || truedocker volume rm atlasmart-mongo-ch12-l3-data 2>/dev/null || truedocker run -d --name atlasmart-mongo-ch12-l3 \ -p 127.0.0.1:27074:27017 \ -v atlasmart-mongo-ch12-l3-data:/data/db \ mongodb/mongodb-community-server:8.3.8-ubuntu2204-slimmongosh "mongodb://127.0.0.1:27074/atlasmart?directConnection=true" --quiet --eval \'printjson({server:db.version(),hello:db.hello().isWritablePrimary}); printjson(db.getSiblingDB("admin").runCommand({getParameter:1,featureCompatibilityVersion:1}))' 2. Seed skew and create scalar plus multikey indexes
const c=db.orders_ch12_l3;c.drop();const base=new Date("2026-07-01T00:00:00Z");let b=[];for (let i=0;i<20000;i++) { const r=i%100; const status=r<80?"fulfilled":(r<95?"processing":"fraud-review"); b.push({_id:i,tenantId:`tenant-${i%20}`,status,createdAt:new Date(base.getTime()+i*60000),tags:[i%10===0?"vip":"standard",i%7===0?"expedite":"normal"],totalCents:1000+(i%9000)}); if (b.length===1000) { c.insertMany(b); b=[]; }}if (b.length) c.insertMany(b);c.createIndex({status:1},{name:"idx_status"});c.createIndex({tenantId:1,status:1,createdAt:-1},{name:"idx_tenant_status_created"});c.createIndex({tags:1,createdAt:-1},{name:"idx_tags_created"});printjson({count:c.countDocuments({}),statusCounts:c.aggregate([{$group:{_id:"$status",n:{$sum:1}}},{$sort:{_id:1}}]).toArray(),indexes:c.getIndexes().map(x=>({name:x.name,key:x.key}))});
Exactly 16,000 documents are fulfilled, 3,000 are
processing, and 1,000 are
fraud-review. tenant-17 intersects
the repeating fraud-review residue, giving a much narrower
compound predicate than status:"fulfilled".
3. Compare low-selectivity, compound, and multikey query shapes
const c=db.orders_ch12_l3;function stagesOf(explainDoc) { const out=[]; const seen=new Set(); function walk(x) { if (!x || typeof x !== "object" || seen.has(x)) return; seen.add(x); if (typeof x.stage === "string") out.push(x.stage); for (const v of Object.values(x)) { if (v && typeof v === "object") walk(v); } } walk(explainDoc.queryPlanner?.winningPlan); return [...new Set(out)];}function summary(explainDoc) { return { stages: stagesOf(explainDoc), nReturned: explainDoc.executionStats?.nReturned, totalKeysExamined: explainDoc.executionStats?.totalKeysExamined, totalDocsExamined: explainDoc.executionStats?.totalDocsExamined, executionTimeMillis: explainDoc.executionStats?.executionTimeMillis, planCacheShapeHash: explainDoc.queryPlanner?.planCacheShapeHash, planCacheKey: explainDoc.queryPlanner?.planCacheKey, queryShapeHash: explainDoc.queryShapeHash };}function run(label,cursor) { const ex=cursor.explain("executionStats"); print(label); printjson(summary(ex)); }run("LOW SELECTIVITY status=fulfilled",c.find({status:"fulfilled"}));run("SELECTIVE tenant+fraud+sort",c.find({tenantId:"tenant-17",status:"fraud-review"}).sort({createdAt:-1}));run("MULTIKEY tags=vip sorted by createdAt",c.find({tags:"vip"}).sort({createdAt:-1}));const s1=c.find({tenantId:"tenant-17",status:"fraud-review"}).sort({createdAt:-1}).limit(10).explain("queryPlanner");const s2=c.find({tenantId:"tenant-3",status:"fraud-review"}).sort({createdAt:-1}).limit(10).explain("queryPlanner");printjson({shape1:s1.queryPlanner?.planCacheShapeHash,shape2:s2.queryPlanner?.planCacheShapeHash,key1:s1.queryPlanner?.planCacheKey,key2:s2.queryPlanner?.planCacheKey});
For the fulfilled query, inspect whether the planner prefers
idx_status or a collection scan and compare work to
16,000 returned documents. For the tenant/fraud query, the
compound index should narrow the work sharply and provide the
requested createdAt order. For
tags:"vip", expect multikey index-key expansion. Do
not copy an expected stage tree blindly; verify the output on
your server.
4. Multikey sort boundaries are stricter
When sorting on an array field through a multikey index, MongoDB
generally needs an in-memory sort unless all sort-field bounds
are [MinKey, MaxKey] and no bounded multikey path
shares the sort path prefix. This is a precise boundary, not the
slogan “multikey indexes cannot sort.” In this fixture,
tags is the multikey field while
createdAt is scalar; inspect the actual plan
instead of assuming the trailing field automatically removes
every sort stage.
Two queries with the same fields/operators but different literal tenant values can share a plan-cache shape hash even when their actual selectivity differs. That is why histogram/sampling/cardinality-estimation behavior—and periodic remeasurement—matters.
5. Deliberately wrong: “the most selective field must always be first”
Index key ordering must satisfy the whole query shape: equality predicates, sort requirements, range predicates, prefix reuse, and workload frequency. A highly selective field can be useful, but moving it first can destroy sort support or prefix utility for another high-value query. Chapters 10 and 12 together should be read as an evidence loop, not a universal formula.
Production judgment. Track value skew and workload mix, not only schema. Multikey growth increases storage/write cost, and tenant/security predicates must remain explicit even when a cached plan looks efficient. On sharded collections, per-shard cardinality and routing can dominate local plan quality. Re-test after distribution shifts, large backfills, or index changes.
docker rm -f atlasmart-mongo-ch12-l3docker volume rm atlasmart-mongo-ch12-l3-dataCheck your understanding
-
Why can an index on
statusbe weak for one value and useful for another? - Why may a multikey index examine more keys than documents?
- Does the same plan-cache shape hash mean the literals have the same selectivity?
- What two multikey conditions allow an indexed array-field sort to avoid in-memory sort?
- What should decide compound key order?
Review the answers
1. Because selectivity depends on the value distribution and predicate, not just the existence of an index.
2. One array-bearing document can contribute multiple distinct index entries.
3. No. Shape hashes normalize structure; different literal values can have very different data distributions.
4. All sort-field bounds must be
[MinKey, MaxKey], and bounded multikey fields
must not share the sort pattern path prefix.
5. The complete workload and evidence: equality, sort, range, selectivity, prefix reuse, frequency, and write/storage cost.
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.
- Multikey indexes — Key expansion and multikey sorting constraints.
- Selective indexes — Selectivity examples and measurement framing.
- Use indexes to sort — Index sort-prefix and direction rules.