0Pricing
MongoDB Academy · Lesson

Reading explain() Output to Diagnose Queries

Learners will interpret IXSCAN vs COLLSCAN stages in explain output and identify missing indexes from nReturned and docsExamined ratios.

Reading explain() Output to Diagnose Queries is a free MongoDB Academy lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the MongoDB Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why explain() Matters

Slow queries in MongoDB are usually caused by missing indexes or suboptimal query plans. The explain() method reveals exactly what MongoDB did to execute a query: which index it chose, how many documents it scanned, and how long each stage took. Without explain(), performance tuning is guesswork; with it, you get a precise diagnostic report.

// Three verbosity levels
db.users.find({ age: { $gt: 25 } }).explain();              // 'queryPlanner'
db.users.find({ age: { $gt: 25 } }).explain('executionStats'); // includes timing
db.users.find({ age: { $gt: 25 } }).explain('allPlansExecution'); // all candidate plans

queryPlanner Mode

The default explain() mode returns the query planner output: the winning plan and rejected plans, but without actually executing the query. This is fast and useful for a quick look at the plan structure. The key field is winningPlan, which describes the execution stage tree MongoDB would use.

const result = db.orders.find({ userId: 'u1' }).explain();

// winningPlan shows the chosen execution strategy
console.log(JSON.stringify(result.queryPlanner.winningPlan, null, 2));
// Example:
// { 'stage': 'FETCH',
//   'inputStage': {
//     'stage': 'IXSCAN',
//     'indexName': 'userId_1' } }

IXSCAN vs COLLSCAN

The two most important stage names in explain() output are: IXSCAN (Index Scan) — the query used an index; and COLLSCAN (Collection Scan) — MongoDB scanned every document. A COLLSCAN on a production collection with millions of documents is almost always a bug. Seeing COLLSCAN is the first sign that you need to add or refine an index.

// BAD: COLLSCAN means no usable index
// { 'stage': 'COLLSCAN', 'filter': { 'email': { '$eq': 'a@b.com' } } }

// GOOD: IXSCAN means an index was used
// { 'stage': 'IXSCAN', 'indexName': 'email_1', 'direction': 'forward' }

// Fix: create the missing index
db.users.createIndex({ email: 1 });

executionStats Mode

explain('executionStats') actually runs the query and collects timing data. The most important metrics are: nReturned — documents returned to the client; totalDocsExamined — documents MongoDB inspected; and totalKeysExamined — index entries scanned. An efficient query should have nReturned ≈ totalDocsExamined. A large gap signals wasted work.

const stats = db.orders
  .find({ userId: 'u1', status: 'active' })
  .explain('executionStats');

const s = stats.executionStats;
console.log('Returned:       ', s.nReturned);
console.log('Keys Examined:  ', s.totalKeysExamined);
console.log('Docs Examined:  ', s.totalDocsExamined);
console.log('Execution ms:   ', s.executionTimeMillis);

Interpreting Key Ratios

Three ratios tell you how efficient a query is: keys examined / keys returned (low is good, 1:1 is perfect), docs examined / docs returned (should be close to 1), and docs examined / keys examined (much greater than 1 means the index is filtering well but fetch is expensive). These ratios guide whether you need a better index, a covered query, or a different filter strategy.

// Efficiency check formula
const ratio = s.totalDocsExamined / s.nReturned;
// ratio = 1  -> perfect, index is very selective
// ratio = 10 -> for every doc returned, 10 were scanned (room to improve)
// ratio = 1000+ -> strong signal to add or redesign index

The FETCH Stage

After an IXSCAN identifies matching index entries, MongoDB may need to FETCH the actual documents from disk to check conditions not covered by the index, or to return fields not in the index. A covered query eliminates the FETCH stage entirely. If you see IXSCAN → FETCH with a high totalDocsExamined, consider adding projected fields to the index to enable coverage.

// With IXSCAN only on userId, fetching to check 'status' adds FETCH
// winningPlan:
// { stage: 'FETCH',
//   filter: { status: { $eq: 'active' } },
//   inputStage: { stage: 'IXSCAN', indexName: 'userId_1' } }

// Fix: compound index so status is in the index too
db.orders.createIndex({ userId: 1, status: 1 });
// Now: IXSCAN only, no FETCH needed for the filter

Rejected Plans and the Plan Cache

MongoDB evaluates multiple candidate plans in parallel during a trial run and picks the winner based on how many documents each plan returns per unit of work. The winning plan is cached for that query shape so future executions skip re-evaluation. You can view rejected plans using allPlansExecution verbosity. The cache is invalidated when indexes change or collection statistics update significantly.

// See all candidate plans and why the winner was chosen
const allPlans = db.orders
  .find({ userId: 'u1', status: 'active' })
  .explain('allPlansExecution');

// rejectedPlans shows what MongoDB tried but discarded
console.log(allPlans.queryPlanner.rejectedPlans.length, 'plans rejected');

SORT and SORT_KEY Stages

When MongoDB cannot use an index to satisfy a sort, it adds an in-memory SORT stage to the plan. In-memory sorts are limited to 100 MB by default; beyond that, the query fails unless you enable allowDiskUse. Seeing a SORT stage is a signal to add a compound index whose key order matches the sort, eliminating the in-memory sort entirely.

// explain shows in-memory sort when index doesn't cover the sort order
// { stage: 'SORT', sortPattern: { createdAt: -1 },
//   inputStage: { stage: 'IXSCAN', ... } }

// Fix: compound index that includes the sort field
db.orders.createIndex({ userId: 1, createdAt: -1 });
// Now the SORT stage disappears from the plan

Forcing an Index With hint()

MongoDB's query planner usually chooses the best index, but sometimes it picks a suboptimal plan (especially when statistics are stale). You can force a specific index with .hint(), passing either the index key pattern or the index name. This is useful for debugging to compare plans or as a last resort in production when the planner makes poor choices.

// Force use of a specific index by key pattern
db.orders.find({ userId: 'u1', status: 'active' })
  .hint({ userId: 1, status: 1 })
  .explain('executionStats');

// Force by index name
db.orders.find({ userId: 'u1' })
  .hint('idx_orders_user');

// Force a COLLSCAN (bypass all indexes)
db.orders.find({ userId: 'u1' })
  .hint({ $natural: 1 });

explain() on Aggregation Pipelines

Aggregation pipelines also support explain(). Pass { explain: true } to aggregate() to see how the pipeline stages execute and whether early stages like $match use indexes. The key insight: a $match at the start of the pipeline can push a filter down to an IXSCAN; a $match after a $group cannot.

// explain() on an aggregation pipeline
db.orders.explain('executionStats').aggregate([
  { $match: { userId: 'u1', status: 'active' } }, // <-- pushed to IXSCAN
  { $group: { _id: '$productId', total: { $sum: '$amount' } } },
  { $sort: { total: -1 } }
]);

Common explain() Red Flags

When reviewing explain() output, watch for these warning signs: a COLLSCAN on a large collection; totalDocsExamined much greater than nReturned; an in-memory SORT stage; or executionTimeMillis above your SLA. Each of these indicates a specific remediation: add an index, change the index key order, add sort fields to the compound index, or redesign the query.

// Red flag checklist:
// 1. stage: 'COLLSCAN' -> add index
// 2. totalDocsExamined >> nReturned -> compound index or partial index
// 3. stage: 'SORT' -> extend compound index to cover sort order
// 4. executionTimeMillis > 100 -> investigate stages above

Quick Check

Test your understanding of MongoDB explain() output from this lesson.

Lesson Recap

In this lesson you learned: explain('executionStats') runs the query and provides timing and document counts, COLLSCAN vs IXSCAN in the winning plan instantly tells you whether an index was used, and the nReturned / totalDocsExamined ratio measures index efficiency. Next up we explore text indexes for full-text search.

Frequently asked questions

Is the “Reading explain() Output to Diagnose Queries” lesson free?

Yes — the full text of “Reading explain() Output to Diagnose Queries” is free to read here on the web, and the MongoDB Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the MongoDB Academy course, upgrade to CoddyKit PRO.

What will I learn in “Reading explain() Output to Diagnose Queries”?

Learners will interpret IXSCAN vs COLLSCAN stages in explain output and identify missing indexes from nReturned and docsExamined ratios. You practise MongoDB Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start MongoDB Academy?

No prior experience is required. MongoDB Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Reading explain() Output to Diagnose Queries” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this MongoDB Academy lesson?

Yes. Every MongoDB Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. How MongoDB B-Tree Indexes Work
  2. Creating Single-Field and Compound Indexes
  3. Index Properties: Unique, Sparse, Partial, TTL
  4. Reading explain() Output to Diagnose Queries
← Back to MongoDB Academy