Reading explain(): COLLSCAN or IXSCAN

explain with "executionStats" is MongoDB's report on how it ran a query, where the winning plan's stages say whether it read every document (COLLSCAN) or used an index (IXSCAN, then FETCH), and totalDocsExamined against nReturned says how much work each result cost.

Journeyman · MongoDB · Database · Updated

By

After this lesson you can

  • Read explain output to tell a collection scan from an index scan by its COLLSCAN, IXSCAN and FETCH stages
  • Compare totalDocsExamined and totalKeysExamined with nReturned to judge how well an index fits a query

Key takeaways

  • Add explain("executionStats") to the end of a find, or put it in front of aggregate, to get MongoDB's report on how it ran the query.
  • Read the winning plan's stages from the inside out. COLLSCAN read every document; IXSCAN read the index, and FETCH then fetched the documents it pointed to.
  • totalDocsExamined is how many documents MongoDB read, and nReturned how many it gave back. The closer the two, the better the index fits the query.
  • executionTimeMillis changes from run to run, so judge a query by what it examined, not by its time.

A page feels slow. A teammate asks whether a new query will hold up when the shop doubles in size. You've added an index and want to know it's used. Guessing answers none of these, and explain() answers all three: it's the database's own report on how it ran one query. You'll read it every time you add an index, and every time a page gets slower than it should.

The examples use Riverbend's 100,000 orders from Why Queries Get Slow, and What an Index Is. In mongosh, after use riverbend, load them again, so the orders start with only their _id index, and add the customerId index back:

load("riverbend-big-store.js")
products: 12, customers: 2000, orders: 100000
true
db.orders.createIndex({ customerId: 1 })
customerId_1

Asking for the report

A delivery driver's trip log says which route they took, how many stops they made and how many parcels they handed over. A query's report says the same things about a query. Put .explain("executionStats"), read "explain execution stats", at the end of a find. Here's a question the shop has no index for, the returned orders of $1,200 or more:

db.orders.find({ status: "returned", total: { $gte: 1200 } }).explain("executionStats")
{
  ...
  queryPlanner: {
    ...
    winningPlan: {
      queryPlan: {
        stage: 'COLLSCAN',
        ...
      },
      ...
    },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 307,
    executionTimeMillis: 31,
    totalKeysExamined: 0,
    totalDocsExamined: 100000,
    ...
  },
  ...
}

The whole report runs to about a hundred lines, so the reports on this page keep only the fields that matter, with ... where lines are left out. Its layout also differs between MongoDB versions: here, on MongoDB 7.0, the stages sit inside queryPlan, and on newer versions such as 9.0 they can sit right under winningPlan. Whatever the version, look for the same two things: the stage names and the numbers.

The stages: COLLSCAN, IXSCAN and FETCH

winningPlan is the plan MongoDB chose. When more than one index could answer a query, MongoDB tries them for a moment and keeps the fastest; the others are listed in rejectedPlans. Its stage names what it did. COLLSCAN, read "collection scan", means it read every document in the collection, the repair manual read page by page.

Now a question the customerId index can answer:

db.orders.find({ customerId: 42 }).explain("executionStats")
{
  ...
  queryPlanner: {
    ...
    winningPlan: {
      queryPlan: {
        stage: 'FETCH',
        ...
        inputStage: {
          stage: 'IXSCAN',
          ...
          keyPattern: { customerId: 1 },
          indexName: 'customerId_1',
          ...
        }
      },
      ...
    },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 50,
    executionTimeMillis: 7,
    totalKeysExamined: 50,
    totalDocsExamined: 50,
    ...
  },
  ...
}

Stages nest: each one's inputStage is the stage that feeds it, so read them from the inside out. IXSCAN, read "index scan", found the 50 entries for 42 in the index named customerId_1. FETCH then fetched the 50 documents those entries point to.

The numbers: examined against returned

executionStats counts the work:

Field What it counts
nReturned documents the query gave back
totalKeysExamined index entries MongoDB read
totalDocsExamined documents MongoDB read
executionTimeMillis how long it took, in milliseconds

The scan read 100,000 documents to give back 307, about 326 read for each one returned. Customer 42's query read 50 and gave back 50. The closer totalDocsExamined comes to nReturned, the better the index fits the query.

An index can help and still not fit well. Here are customer 42's returned orders:

db.orders.find({ customerId: 42, status: "returned" }).explain("executionStats")
{
  ...
  queryPlanner: {
    ...
    winningPlan: {
      queryPlan: {
        stage: 'FETCH',
        ...
        filter: { status: { '$eq': 'returned' } },
        inputStage: {
          stage: 'IXSCAN',
          ...
          indexName: 'customerId_1',
          ...
        }
      },
      ...
    },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 7,
    executionTimeMillis: 0,
    totalKeysExamined: 50,
    totalDocsExamined: 50,
    ...
  },
  ...
}

The index narrowed 100,000 orders to customer 42's 50, and FETCH's filter then threw out the 43 that weren't returned: 50 read for 7. This index knows only the customer, so it can't do better. An index on both fields can, and Compound Indexes and the Order of Fields builds one.

The time was 31 milliseconds for the scan and 0 here, but it changes with every run and with whatever else your computer is doing. The counts don't change, so judge a query by them.

Explaining a pipeline, and Compass

A pipeline's $match can use an index too. Put explain("executionStats") in front of aggregate:

db.orders.explain("executionStats").aggregate([ { $match: { customerId: 42 } }, { $group: { _id: "$status", orders: { $sum: 1 } } } ])
{
  ...
  queryPlanner: {
    ...
    winningPlan: {
      queryPlan: {
        stage: 'GROUP',
        ...
        inputStage: {
          stage: 'FETCH',
          ...
          inputStage: {
            stage: 'IXSCAN',
            ...
            indexName: 'customerId_1',
            ...
          }
        }
      },
      ...
    },
    rejectedPlans: []
  },
  executionStats: {
    executionSuccess: true,
    nReturned: 4,
    executionTimeMillis: 1,
    totalKeysExamined: 50,
    totalDocsExamined: 50,
    ...
  },
  ...
}

The $match became an IXSCAN on customerId_1, and $group worked on the 50 orders it fetched, giving back 4 groups, one per status. That's one more reason $match goes first in a pipeline: at the front, it can use an index.

Compass draws the same report. Open riverbend's orders collection, type { customerId: 42 } in the Filter box, and click Explain. Compass shows the stages as boxes, one feeding the next, and the counts in a summary beside them.

Compass's explain plan for the filter { customerId: 42 } on riverbend's orders: its summary shows 50 documents returned, 50 index keys examined and 50 documents examined, and its diagram shows an IXSCAN stage on the customerId_1 index feeding a FETCH stage.

Check yourself

2 questions to try on the lesson page.

So what

You don't have to guess whether a query is fast or whether your index is used. The stage names say how MongoDB found the documents, and examined against returned says how much work each result cost.

Key terms

Related