Covered Queries: Eliminating the FETCH Stage

A covered query is one where all fields needed by the query (filter, sort, and projection) exist in the index. MongoDB answers the query entirely from the index B-tree without reading the document from WiredTiger storage. The FETCH stage disappears from the execution plan.

Why Covered Queries Matter

When MongoDB executes a non-covered query, each matching index entry requires a document fetch. The IXSCAN returns a pointer (RecordId) to the document’s location in the WiredTiger data file. MongoDB then reads the document from storage, which may involve:

1. A WiredTiger cache hit: the page containing the document is already in memory. Cost: ~1us.
2. A WiredTiger cache miss: the page must be read from disk. Cost: 50-200us (SSD), 5-15ms (HDD).

For a query returning 500 documents, 500 FETCH operations are performed. If 10% are cache misses on SSD, that is 50 disk reads at 100us each: 5ms of additional latency. On a cold cache or with a working set exceeding RAM, the miss rate climbs to 50% or higher.

A covered query skips all 500 FETCH operations. The entire result comes from the index, which is typically smaller than the data and more likely to fit in the WiredTiger cache.

Anatomy of a Covered Query

Three requirements must be met:

1. All fields in the query predicate are in the index.
2. All fields in the sort specification are in the index.
3. All fields in the projection are in the index.
4. The projection explicitly excludes _id (or _id is part of the index).

// Covered query: all fields in the index
collection.find(Filters.eq("sensorId", "sensor-00042"))
    .sort(Sorts.descending("ts"))
    .projection(Projections.fields(
        Projections.include("sensorId", "ts"),
        Projections.excludeId()              // Must exclude _id unless it's in the index
    ))
    .limit(50);

With index {sensorId: 1, ts: -1}, this query is fully covered.

// explain() output for covered query
{
  "winningPlan": {
    "stage": "PROJECTION_COVERED",     // Covered projection
    "inputStage": {
      "stage": "IXSCAN",
      "keyPattern": { "sensorId": 1, "ts": -1 }
    }
  },
  "executionStats": {
    "totalKeysExamined": 50,
    "totalDocsExamined": 0,            // Zero documents examined
    "executionTimeMillis": 0
  }
}

totalDocsExamined: 0 confirms coverage. No FETCH stage in the plan.

If the projection includes a field not in the index (e.g., temperature), MongoDB must fetch the document:

// NOT covered: temperature not in index
{
  "winningPlan": {
    "stage": "PROJECTION_DEFAULT",
    "inputStage": {
      "stage": "FETCH",               // FETCH stage required
      "inputStage": {
        "stage": "IXSCAN"
      }
    }
  },
  "executionStats": {
    "totalKeysExamined": 50,
    "totalDocsExamined": 50            // 50 documents fetched
  }
}