Query Plans with explain()

Creating an index is only half the job. You also need to know whether a query actually uses it, and how much work the server does to answer it. MongoDB tells you through explain(), which returns the query plan: the steps the server chose and, if you ask, the numbers from running them.

In this lesson you will read plans for the same query before and after adding an index, spot in-memory sorts, recognise covered queries, and learn the three numbers that tell you whether a query is healthy. Start with freshly loaded data and use shop, which has no indexes except _id.

Asking for a plan

Add .explain() to the end of a find() cursor. It accepts a verbosity level:

The full output is long: it includes the server version, the parsed filter, every plan stage with its options and a block of timing details. The parts you need most are these:

• queryPlanner.winningPlan: a tree of stages, where each stage has an inputStage that feeds it.
• executionStats.nReturned: documents returned.
• executionStats.totalKeysExamined: index entries read.
• executionStats.totalDocsExamined: documents read from the collection.
• executionStats.executionTimeMillis: time taken on the server.

Because the full output is so long, this lesson uses a small helper function that walks the stage tree and picks out those numbers. Paste it into mongosh once:

function plan(cursor) {
  const e = cursor.explain('executionStats');
  const wp = e.queryPlanner.winningPlan;
  let s = wp.queryPlan || wp;          // some plans nest the tree under queryPlan
  const stages = [];
  while (s) { stages.push(s.stage); s = s.inputStage; }
  const x = e.executionStats;
  return { stages: stages.join(' <- '), returned: x.nReturned, keys: x.totalKeysExamined, docs: x.totalDocsExamined };
}

Read the stages text from right to left: the rightmost stage runs first and passes its results to the stage on its left.

Before an index: COLLSCAN

Aarav's order history queries by customerId, which has no index yet:

plan(db.orders.find({ customerId: 1 }))

{ stages: 'COLLSCAN', returned: 3, keys: 0, docs: 12 }

COLLSCAN means a collection scan: MongoDB read all 12 orders (docs: 12) to return 3. On twelve documents nobody notices; on twelve million it is a disaster, and it gets worse as the collection grows.

After an index: IXSCAN and FETCH

Create the index and ask again:

db.orders.createIndex({ customerId: 1 })
plan(db.orders.find({ customerId: 1 }))

customerId_1
{ stages: 'FETCH <- IXSCAN', returned: 3, keys: 3, docs: 3 }

Now IXSCAN reads 3 entries from the customerId_1 index, and FETCH loads just those 3 documents. Keys examined, documents examined and documents returned are all equal, which is the ideal: no wasted work. To see which index was used, look at the full plan with db.orders.find({ customerId: 1 }).explain().queryPlanner.winningPlan; its inputStage lists indexName: 'customerId_1' and the indexBounds searched.

The three numbers to watch

Compare returned, keys and docs for every important query:

• docs much bigger than returned: the server reads documents only to throw them away. You probably need an index, or a better one.
• keys much bigger than returned: an index is used, but it is not selective or its field order is wrong for this query.
• all three close together: the query is efficient.

executionTimeMillis is useful too, but it changes from run to run and is tiny on small test data, so the counts are a more reliable guide while you develop.

Sorting: the SORT stage

A sort that no index can provide happens in memory, which appears as a SORT stage. The support screen lists pending orders by total, largest first:

plan(db.orders.find({ status: 'pending' }).sort({ total: -1 }))
db.orders.createIndex({ status: 1, total: -1 })
plan(db.orders.find({ status: 'pending' }).sort({ total: -1 }))

{ stages: 'SORT <- COLLSCAN', returned: 2, keys: 0, docs: 12 }
status_1_total_-1
{ stages: 'FETCH <- IXSCAN', returned: 2, keys: 2, docs: 2 }

Before the index, MongoDB scanned everything and then sorted the matches in memory. Large in-memory sorts use a lot of RAM and may have to spill to disk. With the compound index { status: 1, total: -1 } the entries for pending are already in the right order, so the SORT stage disappears. This is the equality-then-sort part of the ESR rule from Indexes in action.

Covered queries: no documents at all

If every field a query filters on and returns is inside the index, MongoDB can answer from the index alone. This is a covered query. Remember to exclude _id, which is not in this index:

plan(db.orders.find({ customerId: 1 }, { _id: 0, customerId: 1 }))

{ stages: 'PROJECTION_COVERED <- IXSCAN', returned: 3, keys: 3, docs: 0 }

docs: 0 shows that no document was read. Covered queries are the fastest kind, which is why it is sometimes worth adding a field to a compound index just so a hot query never touches the collection.

How MongoDB chooses a plan

When several indexes could serve a query, the planner tries the candidate plans for a short trial, picks the one that produces results with the least work and caches that choice for queries of the same shape (the same fields and operators, with different values). The losers appear in queryPlanner.rejectedPlans. You can force a particular index with hint(), for example db.orders.find({ customerId: 1 }).hint({ customerId: 1 }), but only do this after measuring, because the planner is usually right.

Some query types show different stage names. In MongoDB 8.0 and later, simple lookups such as an _id equality skip normal planning and show an express stage such as EXPRESS_IXSCAN. Aggregation pipelines have their own explain format with a stages array; run db.orders.explain('executionStats').aggregate([...]) to see it.

Common mistakes

• Only checking queryPlanner. The default verbosity does not run the query. Use 'executionStats' to see real counts.
• Trusting timing on tiny data. With a dozen documents every plan is fast. Compare documents and keys examined instead.
• Forgetting _id in covered queries. _id is returned by default, so unless it is in the index or excluded, the query must fetch documents.
• Adding hint() everywhere. A hint stops the planner adapting when data changes. Fix the index instead.

Key points

• cursor.explain('executionStats') shows the winning plan and how much work it did.
• Read stages from the inside out: COLLSCAN or IXSCAN first, then FETCH, SORT or PROJECTION stages.
• Compare nReturned, totalKeysExamined and totalDocsExamined; close numbers mean an efficient query.
• A SORT stage means an in-memory sort; a compound index in ESR order can remove it.
• A covered query reads only the index and examines zero documents.
• The planner caches the best plan per query shape; hint() forces an index but should be rare.

plan(db.products.find({ category: 'Accessories' }).sort({ price: 1 }))
db.products.createIndex({ category: 1, price: 1 })
plan(db.products.find({ category: 'Accessories' }).sort({ price: 1 }))
plan(db.products.find({ category: 'Accessories' }, { _id: 0, category: 1, price: 1 }).sort({ price: 1 }))