Covered Queries

Covered Queries

Covered Queries and Index Selection

A covered query is answered from the index alone: every field in the filter and in the projection lives in the index, so no document is ever fetched.

A covered query, and the one field that breaks it

db.orders.createIndex({ region: 1, total: 1 }, { name: 'region_total' });
const q = { region: 'apac', total: { $gte: 4500 } };          // 1,675 matches
for (const p of [{ _id: 0, region: 1, total: 1 },
                 { _id: 0, region: 1, total: 1, status: 1 }, { region: 1, total: 1 }]) {
  const s = db.orders.find(q, p).explain('executionStats').executionStats;
  print(`${JSON.stringify(p)} -> ${s.executionStages.stage} ` +
        `docs=${s.totalDocsExamined} ms=${s.executionTimeMillis}`); }

Output

{"_id":0,"region":1,"total":1} -> PROJECTION_COVERED docs=0 ms=1
{"_id":0,"region":1,"total":1,"status":1} -> PROJECTION_SIMPLE docs=1675 ms=4
{"region":1,"total":1} -> PROJECTION_SIMPLE docs=1675 ms=3

Zero documents examined for 1,675 results. Add one field outside the index and the plan degrades to 1,675 fetches; forget _id: 0 and it degrades the same way, because _id is not in region_total either. So a covered query must exclude _id explicitly, and none is possible over a multikey field, whose index stores elements rather than the array.

MongoDB 1,815 does not cost plans from statistics the way a relational optimizer does. For a new query shape it builds every viable plan, runs them in parallel for a trial period, and keeps whichever produces results with the least work. The winner enters the plan cache keyed by that shape, inactive until a second query confirms it; if it later performs far worse than in its trial, the server evicts it and replans. Inspect the cache with db.orders.aggregate([{ $planCacheStats: {} }]), clear it with db.orders.getPlanCache().clear(). Because the trial is empirical, an index that looks right can lose; hint() forces your choice and survives schema changes that should have changed the plan.

MENU