Performance and explain()

Find and fix slow MongoDB queries: read explain plans, compare keys and documents examined with results returned, spot collection scans and in-memory sorts, build covered queries, use the profiler and slow query log, and apply a repeatable tuning process.

Lesson 21 of 43Advanced17 min read

A query that takes 3 milliseconds with a thousand documents can take 3 seconds with ten million. Slow queries make pages load slowly, tie up the server and, under load, slow down everything else. The good news is that MongoDB tells you exactly how it runs each query, so performance problems can be diagnosed with facts rather than guesses.

The main tool is explain(). It shows the query plan MongoDB chose: whether it used an index or scanned the whole collection, whether it sorted in memory, and with executionStats, how many index keys and documents it examined to return how many results. The ideal is examining about as many documents as you return. Examining a million documents to return ten is the signature of a missing or wrong index.

Beyond single queries, the profiler and slow query log record operations that exceed a time threshold, and $indexStats shows which indexes are actually used. In this lesson you'll read explain output, compare plans before and after adding indexes, build covered queries that never touch documents, and follow a simple process for tuning any slow query.

Reading explain()

MongoDB Shell
db.orders.find({ customerId: 42 }).sort({ createdAt: -1 }).explain('executionStats')

Key parts of the output:

  • queryPlanner.winningPlan: the tree of stages. COLLSCAN means a full scan; IXSCAN means an index scan; FETCH reads documents; SORT means an in-memory sort; PROJECTION_COVERED means no documents were read.
  • executionStats.nReturned: documents returned.
  • totalKeysExamined and totalDocsExamined: work done. Good queries have both close to nReturned.
  • executionTimeMillis: how long the query took on the server.
  • Aggregations explain too: db.orders.explain('executionStats').aggregate([...]).

Covered queries and other quick wins

A covered query is answered entirely from an index: the filter and projection use only indexed fields, and _id is excluded. MongoDB never reads the documents, which is very fast.

Other quick wins:

  • Project only the fields you need, so less data is read and sent.
  • Always limit lists; nobody needs 50,000 results on one page.
  • Filter early in aggregations, so $match can use an index.
  • Avoid unanchored regex and $where on large collections.
  • Use hint() to test how a query performs with a particular index.

Profiler, slow queries and index usage

MongoDB Shell
db.setProfilingLevel(1, { slowms: 100 })      // record operations slower than 100 ms
db.system.profile.find().sort({ ts: -1 }).limit(5)
db.orders.aggregate([{ $indexStats: {} }])    // how often each index is used

The slow query log on the server, Atlas's Query Profiler and Performance Advisor show the slowest query shapes and suggest indexes. Performance also depends on the working set, the data and indexes used regularly, fitting in memory; if it doesn't, queries wait on disk.

MongoDB Performance Syntax

Diagnostic commands

db.c.find(q).explain()                         // queryPlanner only
db.c.find(q).sort(s).explain('executionStats')
db.c.explain('executionStats').aggregate(pipeline)
db.c.find(q).hint({ status: 1, createdAt: -1 })
db.c.find(q, { _id: 0, status: 1 })            // can be covered by an index on status
db.setProfilingLevel(1, { slowms: 50 })
db.system.profile.find({ millis: { $gt: 50 } })
db.c.aggregate([{ $indexStats: {} }])
db.c.stats().totalIndexSize

MongoDB Performance Summary

  • explain('executionStats') shows the winning plan and the work done.
  • COLLSCAN and SORT stages often mean a missing index; IXSCAN means an index was used.
  • Compare totalKeysExamined and totalDocsExamined with nReturned: the closer, the better.
  • Covered queries use only indexed fields and skip reading documents.
  • Use the profiler, slow query log and $indexStats to find problems and confirm fixes.

MongoDB Performance Examples

Each example shows the code, the output it produces and what each part does.

Example 1

Before and after an index

MongoDB Shell
const docs = []
for (let i = 0; i < 20000; i++) docs.push({ customerId: i % 1000, status: i % 4 === 0 ? 'open' : 'closed', total: i % 300 })
db.tickets.insertMany(docs).acknowledged

function summary(cursor) {
  const e = cursor.explain('executionStats')
  const stages = JSON.stringify(e.queryPlanner.winningPlan).match(/"stage":"(\w+)"/g).map(s => s.slice(9, -1))
  return `${stages.join(' > ')} | keys ${e.executionStats.totalKeysExamined}, docs ${e.executionStats.totalDocsExamined}, returned ${e.executionStats.nReturned}`
}

summary(db.tickets.find({ customerId: 8, status: 'open' }).sort({ total: -1 }))
db.tickets.createIndex({ customerId: 1, status: 1, total: -1 })
summary(db.tickets.find({ customerId: 8, status: 'open' }).sort({ total: -1 }))
Output
true
SORT > COLLSCAN | keys 0, docs 20000, returned 20
customerId_1_status_1_total_-1
FETCH > IXSCAN | keys 20, docs 20, returned 20
Line-by-line explanation
  1. Lines 1 to 3Twenty thousand support tickets for a thousand customers.
  2. Line 7The stage names are pulled out of the winning plan to show it compactly.
  3. Line 11Without an index, all 20,000 documents are examined to find 20, and the results are sorted in memory.
  4. Line 12A compound index in ESR order: two equality fields, then the sort field.
  5. Line 13Now exactly the twenty matching index entries and documents are read, already sorted.
Example 2

A covered query

MongoDB Shell
for (let i = 0; i < 5000; i++) db.users.insertOne({ email: `user${i}@example.com`, country: ['UK', 'FR', 'DE'][i % 3], bio: 'x'.repeat(200) })
db.users.createIndex({ country: 1, email: 1 })

function plan(projection) {
  const e = db.users.find({ country: 'FR' }, projection).limit(3).explain('executionStats')
  return `${JSON.stringify(e.queryPlanner.winningPlan).includes('PROJECTION_COVERED') ? 'covered' : 'not covered'}, docs read: ${e.executionStats.totalDocsExamined}`
}
plan({ email: 1 })
plan({ _id: 0, email: 1 })
plan({ _id: 0, email: 1, bio: 1 })
Output
country_1_email_1
not covered, docs read: 3
covered, docs read: 0
not covered, docs read: 3
Line-by-line explanation
  1. Line 2The index holds country and email.
  2. Line 8_id isn't in the index, and it's returned unless excluded, so documents must be read.
  3. Line 9With _id excluded, everything needed is in the index: no documents are read at all.
  4. Line 10bio isn't in the index, so the documents must be fetched again.
Example 3

Explaining an aggregation

MongoDB Shell
for (let i = 0; i < 3000; i++) db.sales.insertOne({ region: ['North', 'South', 'East'][i % 3], amount: i % 50, year: 2024 + (i % 2) })
db.sales.createIndex({ year: 1, region: 1 })

const e = db.sales.explain('executionStats').aggregate([
  { $match: { year: 2025 } },
  { $group: { _id: '$region', total: { $sum: '$amount' } } }
])
const first = e.stages ? e.stages[0].$cursor.queryPlanner.winningPlan : e.queryPlanner.winningPlan
JSON.stringify(first).includes('IXSCAN')
db.sales.aggregate([{ $match: { year: 2025 } }, { $group: { _id: '$region', total: { $sum: '$amount' } } }, { $sort: { _id: 1 } }])
Output
year_1_region_1
true
[
  { _id: 'East', total: 12500 },
  { _id: 'North', total: 12500 },
  { _id: 'South', total: 12500 }
]
Line-by-line explanation
  1. Line 4explain works on aggregations too.
  2. Line 5Because $match comes first, it can use the index on year.
  3. Line 8The plan includes an IXSCAN, confirming the index is used.

Common MongoDB Performance Mistakes

Judging speed on tiny test data

Don't
// "it's fast on my laptop with 200 documents", then slow in production with 20 million
Do
// test with realistic volumes and check explain('executionStats') for COLLSCAN and examined counts

Returning whole documents for lists

Don't
db.products.find({ category: 'home' })                          // every field, every image, every review
Do
db.products.find({ category: 'home' }, { name: 1, price: 1, thumb: 1 }).limit(24)

Adding indexes without checking they're used

Don't
// 12 indexes added over time "just in case"
Do
db.products.aggregate([{ $indexStats: {} }])   // drop indexes with zero accesses after checking

MongoDB Performance in Real Projects

Performance work in production

Teams watch Atlas's Real-Time Performance Panel and Query Profiler, or the slow query log on self-managed servers, to spot new slow query shapes after each release. Each one is explained, fixed with an index or query change and verified. Capacity planning keeps the working set in RAM, and large collections are archived or sharded when they outgrow a single server.

MongoDB Performance FAQ

What does COLLSCAN mean in MongoDB explain output?

COLLSCAN means a collection scan: MongoDB read every document to answer the query because no suitable index was used. That's fine on a tiny collection but slow on a large one. Fix it by creating an index that matches the filter and sort, then run explain again to confirm an IXSCAN stage appears.

What is the difference between queryPlanner, executionStats and allPlansExecution?

They're the three verbosity levels of explain. queryPlanner, the default, shows the chosen plan without running the query. executionStats runs it and adds the real numbers, such as nReturned, totalKeysExamined and executionTimeMillis, and allPlansExecution also reports statistics for the alternative plans the planner tried.

Why is MongoDB not using my index?

Common reasons are an index whose field order doesn't match the query, a filter that skips the index's first field, a negation such as $ne or $nin that matches most of the collection, or an unanchored regex. The planner may also choose another index it measured as faster. Check with explain('executionStats'), and use hint() to compare how each index performs.

How do I find slow queries in MongoDB?

By default mongod logs operations slower than 100 milliseconds, so check the server log first. For more detail, enable the profiler with db.setProfilingLevel(1, { slowms: 100 }) and query db.system.profile, sorted by millis or ts. On Atlas, the Query Profiler and Performance Advisor show the same information with suggestions.

Does the profiler slow down MongoDB?

Level 1, which records only slow operations, adds little overhead and is reasonable to leave on for a while. Level 2 records every operation and can noticeably affect a busy server, so use it only briefly for debugging. Turn profiling off again with db.setProfilingLevel(0) when you're done.

MongoDB Performance Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the call to see execution statistics for a query.

db.orders.find({ status: 'open' }).explain('')
Multiple choiceeasy

What does a COLLSCAN stage mean?

Multiple choicemedium

A query returns 10 documents but examines 900,000. What's the likely fix?

Multiple choicemedium

What makes a query covered?

Multiple choicehard

What does a SORT stage above an IXSCAN suggest?

MongoDB Performance Quiz

5 questions to check what you learned.

  1. Which method shows how MongoDB will run a query?

    • plan()
    • analyze()
    • explain()
    • debug()

  2. What does the profiler record at level 1?

    • Operations slower than the slowms threshold
    • Nothing
    • Every operation
    • Only errors

  3. What does hint() do?

    • Gives a performance tip
    • Creates an index
    • Explains the query
    • Forces the query to use a specific index

  4. Which shows how often each index has been used?

    • getIndexes()
    • explain()
    • $indexStats
    • stats()

  5. What is the working set?

    • All data on disk
    • The data and indexes accessed regularly, which ideally fit in RAM
    • The current transaction
    • The query results

Related MongoDB topics

11IndexesMake queries fast with indexes: create single-field and compound indexes, follow the ESR rule for field order, enforce uniqueness, index only some documents with partial indexes, expire data with TTL indexes, and check index use with explain.12Aggregation BasicsAnalyse data inside MongoDB with the aggregation pipeline: filter with $match, group and total with $group and accumulators like $sum and $avg, sort and limit results, and reshape documents with $project.25Backup & MonitoringProtect and run MongoDB in production: back up with mongodump, snapshots and Atlas continuous backup, restore and test recovery, export data, monitor key metrics such as connections, operation latency, replication lag and disk, read serverStatus and currentOp, and set useful alerts.
Esc
↑↓ to navigateEnter to open