Debugging Slow Queries

Every query you send to MongoDB has to physically locate your documents somewhere on disk (or in memory). If it can use an index, it jumps almost straight to the matching documents. If it can’t, it has to look at every single document in the collection, one by one. A query that returns instantly against a 500-document test collection can quietly take eight seconds once that same collection holds five million documents in production — and the query itself never changes to warn you. Debugging slow queries means using the tools MongoDB gives you to see exactly what the server did to answer a query, and why it took as long as it did.

Overview: How MongoDB Decides to Execute a Query

When you call find(), update(), or run an aggregation, MongoDB’s query planner looks at the filter, sort, and projection you gave it and considers every index that could possibly help. If there’s a cached winning plan for a query with the same shape (same fields, same operators, ignoring the literal values), it reuses that plan. Otherwise, it runs a short "plan trial": it executes each candidate plan for a limited number of documents and picks whichever one produces results with the least work, then caches that plan for future queries of the same shape.

Two execution stages matter most when you’re hunting for slowness: COLLSCAN and IXSCAN. A COLLSCAN (collection scan) means MongoDB walked every document in the collection checking it against your filter — this is the single biggest cause of slow queries at scale. An IXSCAN (index scan) means it walked a B-tree index structure and only fetched the documents that actually matched. After an IXSCAN narrows things down, MongoDB usually needs a FETCH stage to pull the full document off the index key, and it may add a SORT stage (expensive, and capped at 100MB of memory unless you allow disk use) if the result can’t be returned in index order already.

MongoDB also ships two built-in tools for finding slow operations after the fact: the database profiler, which logs operations slower than a threshold (default 100ms) into the capped collection system.profile, and the mongod log itself, which writes a SLOW QUERY line for any operation over that same threshold even if the profiler is off. For queries that are slow right now, db.currentOp() shows every in-flight operation on the server, including how long each has been running, so you can identify (and if necessary kill) a runaway query live.

The single most useful number when debugging is the relationship between nReturned, totalKeysExamined, and totalDocsExamined in an explain() result. If a query returns 10 documents but had to examine 2 million keys or documents to find them, the index isn’t selective enough — or there isn’t one at all.

Syntax

The main diagnostic tool is explain(), called on a cursor or aggregation:

db.collection.find(<query>).explain(<verbosity>);
db.collection.aggregate(<pipeline>).explain(<verbosity>);
Verbosity What it does
"queryPlanner" (default) Shows the winning plan only. Does not execute the query, so it’s safe on huge collections and doesn’t report timing.
"executionStats" Actually runs the winning plan and reports nReturned, totalKeysExamined, totalDocsExamined, and executionTimeMillis. The most commonly used mode for debugging.
"allPlansExecution" Like executionStats, but also reports partial stats for every plan that was tried and rejected during the plan trial.

The profiler is controlled with:

db.setProfilingLevel(<level>, { slowms: <milliseconds> });

level is 0 (off), 1 (log only operations slower than slowms), or 2 (log every operation — useful briefly in development, never in production, since it adds overhead to every request).

Examples

Example 1: Finding a collection scan

Suppose db.orders has 4 million documents and no index beyond the default _id. A support ticket says looking up a customer’s pending orders is slow:

use ecommerce
db.orders.find({ status: "pending", customerId: 8842193 }).explain("executionStats");

Output (trimmed to the fields that matter):

{
  executionStats: {
    executionSuccess: true,
    nReturned: 3,
    executionTimeMillis: 812,
    totalKeysExamined: 0,
    totalDocsExamined: 4000000,
    executionStages: { stage: 'COLLSCAN', ... }
  }
}

The story is right there: 4,000,000 documents examined to return 3. totalKeysExamined is 0 because no index was used at all — the stage is COLLSCAN. This is the classic signature of a missing index.

Example 2: Fixing it with a compound index

Since queries filter on both status and customerId together, a compound index on both fields lets MongoDB jump straight to matching entries:

db.orders.createIndex({ status: 1, customerId: 1 });
db.orders.find({ status: "pending", customerId: 8842193 }).explain("executionStats");

Output:

{
  executionStats: {
    nReturned: 3,
    executionTimeMillis: 1,
    totalKeysExamined: 3,
    totalDocsExamined: 3,
    executionStages: {
      stage: 'FETCH',
      inputStage: { stage: 'IXSCAN', indexName: 'status_1_customerId_1', ... }
    }
  }
}

Now the plan is IXSCAN feeding a FETCH, and totalKeysExamined / totalDocsExamined both equal nReturned. That 1:1 ratio is the goal — the index did all the filtering, and the server only touched the three documents it actually needed.

Example 3: Catching slow queries you didn’t know about

Not every slow query shows up in a bug report. Turn on the profiler to catch anything crossing a threshold in production-like traffic:

db.setProfilingLevel(1, { slowms: 100 });
db.system.profile.find({ millis: { $gt: 100 } })
  .sort({ ts: -1 })
  .limit(5)
  .projection({ op: 1, ns: 1, millis: 1, planSummary: 1, ts: 1 });

Output:

[
  {
    op: 'query',
    ns: 'ecommerce.orders',
    millis: 640,
    planSummary: 'COLLSCAN',
    ts: ISODate("2026-08-04T10:02:11.401Z")
  }
]

Each entry in system.profile is a real operation MongoDB actually ran, with a planSummary field that tells you at a glance whether it used an index. Sorting by ts descending and scanning recent entries is often how a COLLSCAN problem gets discovered before a customer even complains.

How It Works Step by Step

When a query arrives at mongod: (1) the query shape is checked against the plan cache; (2) if nothing is cached, the planner generates candidate plans from every index that could satisfy the filter, sort, or projection; (3) each candidate runs a short trial and the one doing the least work (fewest documents/keys touched for the same results) wins and gets cached; (4) the winning plan executes for real — typically an IXSCAN walking the index’s B-tree to find matching keys, then a FETCH pulling the full BSON document for each key, then optionally a SORT or PROJECTION stage; (5) results stream back to the client in batches (101 documents in the first batch by default, capped at 16MB per batch) as the cursor is iterated. A query with no usable index skips step (2)’s benefit entirely and falls back to a full COLLSCAN, walking documents in natural (roughly insertion) order.

Common Mistakes

1. Querying a large collection with no supporting index

This is the mistake from Example 1 above — a filter with no matching index forces a full scan. Always run explain("executionStats") on any query hitting a collection with more than a few thousand documents before shipping it.

// Wrong: no index on { status, customerId } — full COLLSCAN on 4M docs
db.orders.find({ status: "pending", customerId: 8842193 });
// Fixed: compound index lets Mongo use IXSCAN instead
db.orders.createIndex({ status: 1, customerId: 1 });
db.orders.find({ status: "pending", customerId: 8842193 });

2. Wrong field order in a compound index (breaking the ESR rule)

Compound index field order matters. The rule of thumb is Equality, Sort, Range (ESR): put fields you filter on with exact equality first, then the field you sort on, then range-filtered fields last. Putting a range field before the sort field forces MongoDB to sort in memory even though an index exists.

// Wrong order: range field (orderDate) placed before the sort field
db.orders.createIndex({ orderDate: 1, status: 1 });
db.orders.find({ status: "shipped", orderDate: { $gte: ISODate("2026-01-01") } })
  .sort({ status: 1 });
// Fixed: equality field first, matches the sort, range field last
db.orders.createIndex({ status: 1, orderDate: 1 });
db.orders.find({ status: "shipped", orderDate: { $gte: ISODate("2026-01-01") } })
  .sort({ status: 1 });

3. An unanchored regex that can’t use the index

MongoDB can use an index for a $regex only if the pattern is anchored at the start of the string (^). A regex with a leading wildcard has to scan every index entry (or every document) to test the pattern against each one.

// Wrong: leading wildcard forces a scan of every index entry
db.users.find({ email: /.*@gmail\.com$/ });
// Fixed: anchor at the start so the index prefix can be used directly
db.users.find({ email: /^alice\.smith@gmail\.com$/ });
// For general "contains" search, use a text index and $text instead
db.users.createIndex({ email: "text" });

Best Practices

  • Run explain("executionStats") on any query against a collection that will grow beyond a few thousand documents, before it ships, not after a complaint.
  • Watch the ratio of totalDocsExamined/totalKeysExamined to nReturned — the closer to 1:1, the better the index is doing its job.
  • Follow the ESR rule (Equality, Sort, Range) when ordering fields in a compound index.
  • Enable the profiler at level 1 with a sensible slowms threshold in staging and production to catch slow queries you didn’t anticipate.
  • Use db.currentOp() to inspect operations that are slow right now, and db.killOp(<opid>) to stop a runaway one if it’s harming other traffic.
  • Avoid unanchored $regex and $where for filtering — neither can use an index efficiently; prefer a text index and $text, or restructure the data.
  • Don’t over-index: every index speeds up reads but slows down every write on that collection and takes disk and RAM, so only add indexes that back real query patterns.
  • Keep an eye on unbounded $lookup stages in aggregation pipelines against large foreign collections — put an index-backed $match as early as possible in the pipeline to shrink the working set first.

Practice Exercises

  • Given a db.products collection with no indexes and a common query db.products.find({ category: "electronics", inStock: true }), run explain("executionStats"), note the stage and the examined/returned ratio, then create the right compound index and confirm the stage changes to IXSCAN.
  • Enable the profiler with slowms: 50, run a few queries against a large test collection, then query system.profile for the five slowest operations sorted by millis descending. Expected result shape: an array of documents with op, ns, millis, and planSummary fields.
  • You have a compound index { region: 1, price: 1 } and a query that filters on price: { $gte: 100 } and sorts by region. Explain why this violates the ESR rule, and rewrite the index definition so the sort can use the index instead of an in-memory SORT stage.

Summary

  • explain("executionStats") is the primary tool for diagnosing a slow query — it reveals whether MongoDB used COLLSCAN or IXSCAN, and how many keys/documents it had to examine versus how many it returned.
  • A COLLSCAN on a large collection is the most common cause of a slow query; a supporting index turns it into an efficient IXSCAN + FETCH.
  • Compound index field order matters — follow Equality, Sort, Range (ESR) to avoid forcing an expensive in-memory sort.
  • The database profiler (system.profile) and the mongod slow query log catch slow operations you didn’t know were happening; db.currentOp() catches ones happening right now.
  • Unanchored regexes, $where, and unbounded $lookup stages commonly defeat indexes even when one technically exists.
  • More indexes aren’t free — every index adds write overhead, so add them deliberately based on real query patterns, not preemptively.