Skip to content
Tutorials Center
My learning

Topics

Lesson 39 of 70 ยท Indexes and performance

explain() and the Profiler

Read MongoDB explain() output, spot COLLSCAN and in-memory sorts, compare docsExamined with nReturned, and find slow queries with the profiler and currentOp.

You cannot tune what you cannot see. MongoDB gives you a set of tools to look inside a query: explain() shows the plan the server chose and how much work it did, the database profiler records slow operations after the fact, the slow-query log does the same in the server log, and currentOp shows what is running right now.

This lesson teaches you to read explain("executionStats") output and to hunt for slow queries. Because the site has no MongoDB server, the TryIts feed hand-written sample explain documents (shaped like the real thing) into a small analyzer that does by code what you will learn to do by eye.

The mongosh commands and the sample output blocks are real MongoDB features, shown read-only and trimmed. Exact field names and nesting vary between server versions (newer versions can wrap the plan in extra fields such as queryPlan), and the TryIt samples are written by hand, so treat them as models of the layout, not captured output.

Four ways to look inside

explain() The plan and its cost for one query.
Profiler Slow operations saved in system.profile.
Slow log Slow operations in the server log.
currentOp What is running this second.
1 / 2

Running explain

You call explain() on a cursor (or on a collection) with a verbosity:

JavaScript
db.orders.find({ status: "shipped" }).sort({ createdAt: -1 }).limit(5).explain("executionStats")
  • "queryPlanner" (the default) shows the winning plan and the rejected ones, without running the query.
  • "executionStats" runs the winning plan and reports what it did: documents returned, keys and documents examined, time.
  • "allPlansExecution" also reports statistics for the candidate plans that lost.

An explained find really executes, so on a huge collection even a diagnostic can take time. Explaining an update or delete does not apply the change. You can also explain aggregations with db.orders.explain("executionStats").aggregate([...]) (Aggregation Performance and Explain).

Reading the output

A trimmed result for a well-indexed query looks like this:

JavaScript
{
  queryPlanner: {
    namespace: "shop.orders",
    winningPlan: {
      stage: "FETCH",
      inputStage: {
        stage: "IXSCAN",
        indexName: "status_1_createdAt_1",
        direction: "backward"
      }
    },
    rejectedPlans: []
  },
  executionStats: {
    nReturned: 5,
    executionTimeMillis: 0,
    totalKeysExamined: 5,
    totalDocsExamined: 5
  }
}

Read it from the inside out. The plan is a tree of stages: the innermost stage produces data, and each outer stage transforms it.

  • IXSCAN reads entries from an index (indexName says which one).
  • FETCH loads the full documents that the index pointed to.
  • COLLSCAN reads every document in the collection. On a large collection this is almost always the problem.
  • SORT is a blocking in-memory sort: the server had to collect results and sort them, because no index supplied the order.
  • A covered query (see Designing Compound Indexes) has no FETCH, and totalDocsExamined is 0.

Then compare the counters in executionStats:

Field Meaning
nReturned Documents the query returned
totalKeysExamined Index entries read
totalDocsExamined Documents read from the collection
executionTimeMillis Time to run the plan

A healthy query has totalDocsExamined close to nReturned, and totalKeysExamined close to it as well. If a query examines 50,000 documents to return 120, 99.8% of the work was wasted, and either an index is missing or its fields are in the wrong order. If it examines 20,000 keys and documents to return 5, look for a SORT stage: the index narrowed the filter but could not supply the order.

An analyzer for sample explain output

The stand-in below walks the plan tree, prints the stage chain and the efficiency ratio, and gives a verdict, in the same order you would read it by hand.

JS Diagnose three explain documents

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The first sample is a pure collection scan, the second is the picture you want, and the third shows the classic half-fix: an index on status narrows the search but the server still sorts 20,000 documents to keep the newest five. Replacing it with { status: 1, createdAt: 1 } turns the third sample into the second.

The database profiler

explain() examines one query you already suspect. To find the unknown slow queries, turn on the profiler. It records operations into a capped collection called system.profile in each profiled database:

JavaScript
db.setProfilingLevel(1, { slowms: 100 })   // record operations slower than 100 ms
db.getProfilingStatus()

The levels are:

  • 0: off (the default).
  • 1: record only operations slower than slowms (default 100 ms).
  • 2: record everything. Use it briefly on a test system, because it adds a write for every operation.

Then query the profile like any other collection:

JavaScript
db.system.profile.find({ millis: { $gt: 100 } }).sort({ ts: -1 }).limit(5)

Useful fields include op (query, update, insert...), ns (database and collection), millis, keysExamined, docsExamined, nreturned and planSummary (for example COLLSCAN or IXSCAN { status: 1 }). Because system.profile is capped, old entries are pushed out as new ones arrive, so it is a window onto recent history, not a permanent log.

The slow-query log

Even with the profiler off (level 0), mongod writes operations slower than slowms to its log file, as structured JSON lines marked as slow queries, including the duration, plan summary and keys and documents examined. In production this is the first place many teams look, because the log is already collected and searchable. Managed services such as MongoDB Atlas also provide a slow-query view and a Performance Advisor that suggests indexes from the same data.

currentOp and killOp

The profiler looks backwards. currentOp looks at the present:

JavaScript
db.currentOp({ active: true, secs_running: { $gt: 10 } })

This lists operations that have been running for more than ten seconds. Each has an opid. If one is runaway (an accidental unindexed query on a huge collection, for example), you can stop it with db.killOp(<opid>). Be careful with long-running writes and index builds, and make sure you know what an operation is before killing it.

To find indexes that nothing uses, run the $indexStats aggregation stage:

JavaScript
db.orders.aggregate([{ $indexStats: {} }])

It reports how many times each index has been used since the server started. An index with zero use is a candidate for hiding and then dropping (Index Types).

Reading a profile in code

The second stand-in imitates a few system.profile documents and answers the question "which collection and plan cause the most slow time?". Group-by logic like this is exactly what you would do with an aggregation on the real system.profile.

JS Which plans waste the most time?

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The two COLLSCAN queries and the collection-scan update on shop.orders dominate the total, so one index on the filter field would remove most of the slow time. The users entry used an index but still examined 9,000 documents to return one, which suggests the index on country is not selective enough for that lookup: a more specific compound index is needed.

A tuning loop

  1. Find slow operations (profiler, slow log, Atlas, or currentOp).
  2. explain("executionStats") the worst one.
  3. Look for COLLSCAN, SORT and a work-per-result ratio far above 1.
  4. Change one index at a time, then explain again to confirm.
  5. Check $indexStats later to remove indexes that never get used.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In explain output, which stage means every document in the collection was read?

    COLLSCAN is a collection scan, with no index narrowing the search. IXSCAN reads index entries and FETCH loads documents the index pointed to.
  2. 2. A query returns 5 documents but totalDocsExamined is 20000 and the plan has a SORT stage. What is the most likely fix?

    The SORT stage shows the index did not provide the order, so the server read and sorted many documents. An index shaped by the ESR rule lets it read in order and stop early.
  3. 3. What does profiling level 1 record?

    Level 0 is off, level 1 records operations slower than slowms (100 ms by default), and level 2 records all operations.
  4. 4. Which tool shows operations that are running right now?

    currentOp lists active operations at this moment. The profile and the slow-query log are records of operations that already finished.

Summary

  • explain("executionStats") shows the chosen plan (COLLSCAN, IXSCAN, FETCH, SORT) and its cost (nReturned, totalKeysExamined, totalDocsExamined, time).
  • Healthy queries examine roughly as many documents as they return; a COLLSCAN or an in-memory SORT on a big collection is a warning sign.
  • The profiler (levels 0, 1, 2) saves operations into the capped system.profile collection; the slow-query log records slow operations even at level 0.
  • currentOp and killOp handle what is running now; $indexStats finds unused indexes.
  • Tune in a loop: find, explain, change one index, explain again.

Next, see the habits that make queries slow in the first place in Common Performance Pitfalls.