IXSCAN can still be expensive

An IXSCAN in the winning plan means MongoDB used an index — but it does not mean the query is efficient. If the index entry count far exceeds the document count returned, MongoDB is scanning more of the index than necessary and then loading documents to apply a filter that the index could not satisfy. This guide shows how that happens on a compound index and how to measure and fix it.

Setup (illustrative)

The examples below use a synthetic dataset. Numbers are illustrative — always measure against your actual data distribution.

// Collection: orders
// ~50,000 documents, roughly 20% are category "electronics"
// Existing index: { category: 1 }

// Query: electronics orders where price < 50
db.orders.find(
  { category: "electronics", price: { $lt: 50 } }
)

Before: expensive IXSCAN

With only { category: 1 }, the planner uses IXSCAN to find all electronics documents, then FETCH to load them and check price:

{
  "queryPlanner": {
    "winningPlan": {
      "stage": "FETCH",           // loads full docs to check price
      "filter": { "price": { "$lt": 50 } },
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": { "category": 1 },
        "indexName": "category_1",
        "indexBounds": {
          "category": ["[\"electronics\", \"electronics\"]"]
        }
      }
    }
  },
  "executionStats": {
    "nReturned": 120,
    "executionTimeMillis": 48,
    "totalKeysExamined": 10200,   // all electronics keys
    "totalDocsExamined": 10200    // all electronics docs loaded
  }
}

What happened: the IXSCAN efficiently found 10,200 index entries for category = "electronics". But price is not in the index, so FETCH loaded all 10,200 documents to apply the price < 50 filter. Only 120 matched — an 85:1 examination ratio. This is the classic "IXSCAN + FETCH filter" pattern.

A FETCH stage with a filter field is the signal that document loading is doing work the index could have handled. The analyzer flags this pattern when the ratio of examined to returned documents is high.

After: compound index with the filter field

Create a compound index that includes both fields — category first (the equality predicate), price second (the range predicate). The equality field should come first in a compound index for range queries.

db.orders.createIndex({ category: 1, price: 1 })

Re-run the explain after creating the index:

{
  "queryPlanner": {
    "winningPlan": {
      "stage": "FETCH",           // still needed to return all fields
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": { "category": 1, "price": 1 },
        "indexName": "category_1_price_1",
        "indexBounds": {
          "category": ["[\"electronics\", \"electronics\"]"],
          "price": ["[-inf.0, 50)"]
        }
      }
    }
  },
  "executionStats": {
    "nReturned": 120,
    "executionTimeMillis": 2,
    "totalKeysExamined": 120,     // only matching keys read
    "totalDocsExamined": 120      // only matching docs loaded
  }
}

What changed: the IXSCAN now bounds on both category and price — it reads exactly the keys that will match. The FETCH stage remains (to retrieve other document fields), but totalDocsExamined dropped from 10,200 to 120. The examination ratio is now 1:1.

What to verify before and after

1. 1.Check that the new index is actually used — look for its name in keyPattern or indexName in the winning plan.
2. 2.Verify totalDocsExamined ≈ nReturned after the change. A persistent gap means another filter is still being applied post-IXSCAN.
3. 3.Test with your actual production data distribution. A synthetic example with 20% electronics may not match your real cardinality.
4. 4.Check for index intersection — MongoDB may choose to combine two single-field indexes instead of using your compound index. The AND_SORTED stage in the plan tree indicates this.
5. 5.Measure write overhead. Each additional index slows down inserts and updates on the collection. A compound index replaces two single-field indexes only when the prefix fields are also useful independently.