StackPilots | From 900ms to 9ms: Index and Aggregation Tuning for MongoDB 8 in a MEAN App

Most "the app is slow" tickets we get on MEAN engagements end at the same place: one or two MongoDB queries doing a collection scan behind an Express route that looks perfectly innocent. Distributed tracing will point you at the route — we covered that in tracing a slow MEAN app with OpenTelemetry — but it will not tell you which index you are missing. This tutorial is the next step: taking a slow Mongoose 8 query on MongoDB 8 and driving it down to single-digit milliseconds, with the measurements to prove it.

Everything below runs against MongoDB 8.0 with Mongoose 8 on Node.js 24.

The example: an orders collection that got big

Assume a fairly ordinary schema.

import { Schema, model } from 'mongoose';

const orderSchema = new Schema({
  tenantId: { type: Schema.Types.ObjectId, required: true },
  status:   { type: String, enum: ['pending', 'paid', 'shipped', 'cancelled'], required: true },
  total:    { type: Number, required: true },
  customer: { type: Schema.Types.ObjectId, ref: 'Customer', required: true },
  placedAt: { type: Date, required: true, default: Date.now },
}, { timestamps: true });

export const Order = model('Order', orderSchema);

And the route that is timing out at two million documents:

app.get('/api/orders', requireAuth, async (req, res) => {
  const orders = await Order.find({ tenantId: req.user.tenantId, status: 'paid' })
    .sort({ placedAt: -1 })
    .limit(50)
    .lean();
  res.json(orders);
});

Fifty documents out. It should be trivially fast. It is not, and explain says why.

Step 1: read the explain plan, not the timings

Mongoose exposes explain() on any query. Use executionStats, and read it in Node rather than in a shell, because the query you run in Compass is rarely byte-identical to the one Mongoose builds.

const plan = await Order.find({ tenantId, status: 'paid' })
  .sort({ placedAt: -1 })
  .limit(50)
  .explain('executionStats');

const stats = plan.executionStats;
console.log({
  ms: stats.executionTimeMillis,
  returned: stats.nReturned,
  examinedDocs: stats.totalDocsExamined,
  examinedKeys: stats.totalKeysExamined,
  stage: stats.executionStages.stage,
});

On the unindexed collection this prints something like:

{ ms: 912, returned: 50, examinedDocs: 2013447, examinedKeys: 0, stage: 'SORT' }

Three numbers matter, and they matter in this order.

stage: 'COLLSCAN' anywhere in the tree means no index was used at all.
totalDocsExamined vs nReturned. A healthy ratio is close to 1:1. Two million examined to return fifty is a 40,000:1 ratio.
A blocking SORT stage. This means MongoDB pulled the whole result set into memory to sort it. Past 100 MB it fails outright unless allowDiskUse is set — and if you have hit that, you have already lost.

Ignore executionTimeMillis as your primary signal. It moves with cache state and machine load. Documents examined is deterministic, and it is what you are actually optimising.

Step 2: build the index with the ESR rule

The standard guidance for compound index key order is ESR: Equality, Sort, Range. Put the fields you match exactly first, the fields you sort by next, and range predicates ($gt, $in, date windows) last.

Our query has equality on tenantId and status, and a descending sort on placedAt:

orderSchema.index({ tenantId: 1, status: 1, placedAt: -1 });

Re-run the explain:

{ ms: 9, returned: 50, examinedDocs: 50, examinedKeys: 50, stage: 'LIMIT' }

The blocking SORT is gone, because an index that ends in placedAt: -1 already stores the keys in the order the query wants; MongoDB walks it and stops after fifty. Fifty examined, fifty returned.

A few notes people trip over:

Sort direction can be inverted, but not mixed arbitrarily. An index on { placedAt: -1 } also serves sort({ placedAt: 1 }) — MongoDB scans it backwards. But an index on { a: 1, b: 1 } will not serve sort({ a: 1, b: -1 }); you need { a: 1, b: -1 }.
Prefixes are free. { tenantId: 1, status: 1, placedAt: -1 } also serves queries on tenantId alone and on tenantId + status. Do not create those as separate indexes.
Do not rely on autoIndex in production. Mongoose builds indexes on connect by default, which is fine locally and a foot-gun on a large live collection. Set autoIndex: false in production and create indexes deliberately, in the background, through your migration process or Atlas.

await mongoose.connect(uri, { autoIndex: process.env.NODE_ENV !== 'production' });

Step 3: go one better with a covered query

If a query can be answered entirely from index keys, MongoDB never touches the documents at all. That is a covered query, and it shows up in the plan as an IXSCAN with no FETCH parent.

Say the list view only needs the id, total and date:

orderSchema.index({ tenantId: 1, status: 1, placedAt: -1, total: 1 });

const rows = await Order.find({ tenantId, status: 'paid' })
  .select({ _id: 0, placedAt: 1, total: 1 })
  .sort({ placedAt: -1 })
  .limit(50)
  .lean();

Two requirements catch people out: every field in the projection must be in the index, and you must explicitly exclude _id unless _id is part of the index. Covered queries are worth chasing for high-traffic list and count endpoints, not for everything — the wider index costs write throughput and RAM.

And always use .lean() on read-only routes. Hydrating Mongoose documents you are about to JSON.stringify allocates a full document instance per row for nothing; on a 50-row list it is noise, on a 5,000-row export it is a measurable chunk of your response time.

Step 4: fix the aggregation, not just the find

Dashboards are where MEAN apps really fall over. A typical revenue-by-day pipeline:

const report = await Order.aggregate([
  { $lookup: { from: 'customers', localField: 'customer', foreignField: '_id', as: 'customer' } },
  { $unwind: '$customer' },
  { $match: { tenantId, status: 'paid', placedAt: { $gte: from, $lt: to } } },
  { $group: { _id: { $dateTrunc: { date: '$placedAt', unit: 'day' } }, revenue: { $sum: '$total' } } },
  { $sort: { _id: 1 } },
]);

This joins every order in the collection to a customer and then filters. Two rules fix the majority of slow pipelines:

$match first, always. The first stage should be the most selective filter you have, and it should be indexable. Only the leading stages of a pipeline can use an index.
$project before you $group. Drop fields you do not need so less data flows between stages.

const report = await Order.aggregate([
  { $match: { tenantId, status: 'paid', placedAt: { $gte: from, $lt: to } } },
  { $project: { placedAt: 1, total: 1 } },
  { $group: { _id: { $dateTrunc: { date: '$placedAt', unit: 'day' } }, revenue: { $sum: '$total' } } },
  { $sort: { _id: 1 } },
]);

The $lookup disappeared because the report never used the customer document. That is the most common finding in a pipeline review: a join nobody needs. When you genuinely do need related data on a hot read path, consider denormalising the one or two fields you display (customerName alongside customer) and keeping them in sync on write. A $lookup is a nested loop; on a hot dashboard it is usually the wrong trade.

Explain works on aggregations too:

const plan = await Order.aggregate(pipeline).explain('executionStats');

Check that the first stage reports an IXSCAN against your ESR index — here { tenantId: 1, status: 1, placedAt: -1 } serves it, with placedAt correctly last as the range predicate.

Step 5: find the rest of them with the profiler

You cannot hand-inspect every query. Turn on the database profiler for queries over 100ms:

await mongoose.connection.db.command({ profile: 1, slowms: 100, sampleRate: 1.0 });

const worst = await mongoose.connection.db
  .collection('system.profile')
  .find({ millis: { $gt: 100 }, ns: /orders/ })
  .sort({ millis: -1 })
  .limit(20)
  .toArray();

for (const op of worst) {
  console.log(op.millis, op.planSummary, JSON.stringify(op.command?.filter ?? op.command?.pipeline));
}

Sort the output by planSummary and grep for COLLSCAN — that list is your work queue. On Atlas the same data is in the Profiler tab plus the Performance Advisor, which will suggest indexes; treat its suggestions as candidates to verify with explain, not as instructions. It optimises each query in isolation and will happily suggest five overlapping indexes where one compound index serves all of them.

Leave the profiler at profile: 1 with a sensible slowms in production, or sampleRate: 0.1 on a very busy cluster. Do not leave it at profile: 2 (log everything); the capped system.profile collection is a write on every operation.

What not to do

Do not index everything. Every index is written on every insert and update, and competes for the WiredTiger cache. We regularly delete more indexes than we add. Use $indexStats to find the dead ones:

const usage = await mongoose.connection.db.collection('orders')
  .aggregate([{ $indexStats: {} }]).toArray();
usage.forEach(i => console.log(i.name, i.accesses.ops));

An index with near-zero accesses.ops after a full traffic cycle is pure cost. (Check every replica set member — reads may be routed elsewhere.)

Do not paginate with skip. .skip(50000).limit(50) walks fifty thousand index keys before returning anything. Use range pagination on the sort key instead:

const page = await Order.find({ tenantId, status: 'paid', placedAt: { $lt: cursor } })
  .sort({ placedAt: -1 }).limit(50).lean();

Do not cache to hide a missing index. Putting Redis in front of a COLLSCAN buys you one release cycle and then you have two problems.

The checklist

When a MEAN app gets slow at the database layer, in order:

Confirm the slow operation with tracing or the profiler; do not guess.
explain('executionStats') it and compare totalDocsExamined to nReturned.
Add or reorder a compound index using ESR. Re-explain and confirm the ratio approaches 1:1 and no blocking SORT remains.
For aggregations, move $match to the front, delete unnecessary $lookup stages, and project early.
Sweep system.profile for the remaining COLLSCANs, and sweep $indexStats for indexes nobody uses.
Only then look at caching, read preferences or hardware.

We run this exact pass as part of performance tuning in MEAN stack, usually alongside an upgrade — the query planner improved meaningfully across recent releases, so if you are still on 6.0 see zero-downtime MongoDB 6.0 to 8.0 upgrades first. If your dashboard queries are the problem and you would rather someone else own the fix, get in touch.