Stop Guessing at MongoDB Performance: Read the Explain PlanStop Guessing at MongoDB Performance: Read the Explain Plan

An endpoint went from 40 ms to 1.8 s over a few months. Nothing in the code had changed — the collection had just grown past the point where a missing index stopped being free.

The fix took eleven minutes once I stopped guessing and read the plan.

The one command#

db.blogs
  .find({ published: true, tags: "nextjs" })
  .sort({ publishedAt: -1 })
  .limit(10)
  .explain("executionStats");

executionStats actually runs the query and reports what happened. (queryPlanner, the default, only shows what Mongo intends to do.) In Mongoose:

const plan = await Blog.find({ published: true, tags: 'nextjs' })
  .sort({ publishedAt: -1 })
  .limit(10)
  .explain('executionStats');
 
console.dir(plan, { depth: null });

The four numbers#

The output is verbose. Ignore almost all of it and look at these:

{
  "executionStats": {
    "nReturned": 10,
    "executionTimeMillis": 1834,
    "totalKeysExamined": 0,
    "totalDocsExamined": 84213,
    "executionStages": { "stage": "COLLSCAN" }
  }
}

The single most useful signal is the ratio totalDocsExamined / nReturned. Examining 84,213 documents to return 10 means you're reading the whole collection and throwing away 99.99% of it.

COLLSCAN on anything but a tiny lookup table is a bug.

Fix one: the ESR rule#

A compound index isn't just "the fields in the query." Order matters, and the ordering rule is Equality, Sort, Range:

db.blogs.find({
  published: true,                                    // Equality
  publishedAt: { $gte: ISODate("2026-01-01") }        // Range
}).sort({ views: -1 });                               // Sort
 
// Correct order: E → S → R
db.blogs.createIndex({ published: 1, views: -1, publishedAt: 1 });

Put the range field before the sort field and MongoDB can no longer walk the index in sorted order — it collects everything, then sorts in memory. Re-run the explain and you'll see it:

{ "stage": "SORT", "memLimit": 33554432, "usedDisk": true }

Once a blocking sort exceeds 32 MB, the query fails outright unless allowDiskUse is set — and disk sorts are orders of magnitude slower. A SORT stage in your plan is a missing or misordered index, every time.

Fix two: cover the query#

If the index contains every field the query touches, MongoDB answers from the index alone and never reads a document. That's a covered query, and it shows up as totalDocsExamined: 0.

db.blogs.createIndex({ published: 1, publishedAt: -1, title: 1, slug: 1 });
 
db.blogs
  .find({ published: true }, { _id: 0, title: 1, slug: 1 })
  .sort({ publishedAt: -1 });

Two requirements people miss: the projection must exclude _id (it's included by default and isn't in the index), and every field in filter, sort and projection must be present in the index.

For list and card views — exactly what a blog index page needs — this is close to free.

Fix three: stop over-fetching#

The fastest index in the world can't rescue a query that pulls 40 KB of markdown per row to render a card:

// ✗ Loads full article bodies to render titles
const posts = await Blog.find({ published: true }).sort({ publishedAt: -1 });
 
// ✓ Only what the card renders — and .lean() skips hydrating documents
const posts = await Blog.find({ published: true })
  .select('title slug excerpt tags publishedAt readingMinutes')
  .sort({ publishedAt: -1 })
  .limit(12)
  .lean();

.lean() is a real win at scale — it returns plain objects instead of full Mongoose documents, skipping getters, virtuals and change tracking. For read-only paths it routinely halves response time, and it also gives you objects that are safe to pass into React Server Components.

Fix four: paginate with a cursor, not a skip#

skip(n) walks and discards n documents. Page 1 is instant, page 500 is not:

// ✗ O(n) — degrades linearly with page depth
Blog.find(filter).sort({ publishedAt: -1 }).skip(page * 20).limit(20);
 
// ✓ O(log n) — jumps straight into the index
Blog.find({ ...filter, publishedAt: { $lt: cursor } })
  .sort({ publishedAt: -1 })
  .limit(20);

Return the last item's publishedAt as the next cursor. For infinite-scroll and feed endpoints this is strictly better; for a 15-post blog index, skip is fine. Know which one you're building.

Finding the queries worth fixing#

Don't guess at which endpoint is slow — let the database tell you. Enable the profiler for anything over 100 ms:

db.setProfilingLevel(1, { slowms: 100 });
 
db.system.profile
  .find({ millis: { $gt: 100 } })
  .sort({ ts: -1 })
  .limit(20);

On Atlas, the Performance Advisor does this continuously and suggests indexes with expected impact. It is genuinely good and I check it before adding anything by hand.

Indexes are not free#

Every index costs write throughput and RAM. Before adding one:

db.blogs.aggregate([{ $indexStats: {} }]);

accesses.ops shows how many times each index has actually been used since the last restart. Zero means you're paying for nothing — drop it.

Also watch for prefix redundancy. { a: 1, b: 1, c: 1 } already serves queries on { a } and { a, b }. A separate { a: 1 } index is pure overhead.

The checklist#

1. explain("executionStats") on the slow query.
2. COLLSCAN? Add an index.
3. SORT stage? Reorder it using ESR.
4. totalDocsExamined ≫ nReturned? Tighten the filter or cover the query.
5. Fetching fields you don't render? .select() and .lean().
6. Deep pagination? Switch to a cursor.
7. $indexStats to drop what nothing uses.

Four numbers, one command. Performance work stops being guesswork the moment you read the plan.