0Pricing
MongoDB Academy · Lección

Lectura del resultado de explain() para diagnosticar consultas

Interpretará las etapas IXSCAN y COLLSCAN del resultado de explain e identificará índices ausentes a partir de las proporciones entre nReturned y docsExamined.

Lectura del resultado de explain() para diagnosticar consultas es una lección gratuita de MongoDB Academy en CoddyKit. Esta es la lección 4 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de MongoDB Academy, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de MongoDB Academy incluye 4 lecciones en total.

Partes de esta lección aún no han sido traducidas y se muestran en 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.

Preguntas frecuentes

¿La lección «Lectura del resultado de explain() para diagnosticar consultas» es gratis?

Sí — el texto completo de «Lectura del resultado de explain() para diagnosticar consultas» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de MongoDB Academy, actualiza a CoddyKit PRO. El curso de MongoDB Academy incluye 4 lecciones en total.

¿Qué aprenderé en «Lectura del resultado de explain() para diagnosticar consultas»?

Interpretará las etapas IXSCAN y COLLSCAN del resultado de explain e identificará índices ausentes a partir de las proporciones entre nReturned y docsExamined. Practicas MongoDB Academy con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar MongoDB Academy?

No se requiere experiencia previa. MongoDB Academy en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 4 de 4.

¿Cuánto tiempo toma la lección «Lectura del resultado de explain() para diagnosticar consultas»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de MongoDB Academy?

Sí. Cada lección de MongoDB Academy incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

  1. Cómo funcionan los índices B-Tree de MongoDB
  2. Creación de índices de un solo campo y compuestos
  3. Propiedades de los índices: unique, sparse, partial y TTL
  4. Lectura del resultado de explain() para diagnosticar consultas
← Volver a MongoDB Academy