การอ่านผลลัพธ์จาก explain() เพื่อวินิจฉัยการค้นหา

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.