0Pricing
MongoDB Academy · Aula

Lendo a saída de explain() para diagnosticar consultas

Você interpretará as etapas IXSCAN e COLLSCAN na saída de explain e identificará índices ausentes pelas proporções entre nReturned e docsExamined.

Lendo a saída de explain() para diagnosticar consultas é uma aula grátis de MongoDB Academy no CoddyKit. Esta é a aula 4 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de MongoDB Academy, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de MongoDB Academy inclui 4 aulas no total.

Partes desta aula ainda não foram traduzidas e aparecem em inglês.

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.

Perguntas Frequentes

A aula “Lendo a saída de explain() para diagnosticar consultas” é grátis?

Sim — o texto completo de “Lendo a saída de explain() para diagnosticar consultas” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de MongoDB Academy, atualize para CoddyKit PRO. O curso de MongoDB Academy inclui 4 aulas no total.

O que vou aprender em “Lendo a saída de explain() para diagnosticar consultas”?

Você interpretará as etapas IXSCAN e COLLSCAN na saída de explain e identificará índices ausentes pelas proporções entre nReturned e docsExamined. Você pratica MongoDB Academy com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar MongoDB Academy?

Nenhuma experiência prévia é necessária. MongoDB Academy no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 4 de 4.

Quanto tempo leva a aula “Lendo a saída de explain() para diagnosticar consultas”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de MongoDB Academy?

Sim. Cada aula de MongoDB Academy inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

  1. Como funcionam os índices B-tree do MongoDB
  2. Criando índices de campo único e compostos
  3. Propriedades de índices: exclusivos, esparsos, parciais e TTL
  4. Lendo a saída de explain() para diagnosticar consultas
← Voltar para MongoDB Academy