Taming Slow MongoDB Aggregation Pipelines in Production

Why it's fast locally and slow in production

Aggregation pipelines are deceptively easy to write and deceptively easy to get wrong performance-wise, because the failure mode doesn't show up until the collection is big enough for the wrong stage order or a missing index to actually matter. A pipeline that runs in 20ms against a thousand seeded documents can take 8 seconds against a few million real ones - and the fix is almost never "add more RAM."

Step 1 - read explain(), don't guess

.explain("executionStats") is the actual source of truth here, not intuition about which stage "feels" expensive. It shows exactly which stages hit an index, which ones did a full collection scan, and how many documents got examined at each step.

const stats = await Order.aggregate([
  { $match: { status: "completed", createdAt: { $gte: startDate } } },
  { $group: { _id: "$customerId", total: { $sum: "$amount" } } },
]).explain("executionStats");

console.log(stats.stages[0].$cursor.executionStats);
// totalDocsExamined vs nReturned tells you immediately whether
// the $match stage is using an index or scanning the collection

If totalDocsExamined is dramatically larger than nReturned, that stage is scanning far more documents than it needs to return - that gap is exactly where the missing index belongs.

Step 2 - $match and $sort as early as physically possible

Every stage in a pipeline processes whatever the previous stage handed it. If $match runs last, every earlier stage - every $lookup, every $group - has already processed documents that were going to get filtered out anyway. Reordering costs nothing and often cuts the working set by an order of magnitude before the expensive stages even run.

// Slow - filters the smallest amount last, after the expensive
// $lookup has already joined every single order to its customer
db.orders.aggregate([
  { $lookup: { from: "customers", localField: "customerId", foreignField: "_id", as: "customer" } },
  { $match: { status: "completed" } },
]);

// Fast - filter first, join only what survives
db.orders.aggregate([
  { $match: { status: "completed" } },
  { $lookup: { from: "customers", localField: "customerId", foreignField: "_id", as: "customer" } },
]);

Step 3 - index the fields your $match and $sort actually use

A compound index needs to match the order fields are actually queried and sorted in, not just exist somewhere on the same collection. For a pipeline that matches on status and sorts on createdAt, the index needs both fields, in that order.

db.orders.createIndex({ status: 1, createdAt: -1 });

Without it, MongoDB either scans the whole collection to find matching documents, or finds them via a partial index and then does an in-memory sort - which has its own hard ceiling (32MB) and starts throwing errors once the result set is too large for the sort to fit in memory.

Step 4 - watch what $lookup does to memory

$lookup is the stage most likely to quietly become the bottleneck, because it's doing a join and joins scale with the product of both sides, not the sum. Adding a $match inside the lookup's own pipeline (rather than filtering after the join happens) keeps the joined documents small before they ever get pulled into memory.

db.orders.aggregate([
  { $match: { status: "completed" } },
  {
    $lookup: {
      from: "customers",
      let: { custId: "$customerId" },
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", "$$custId"] }, active: true } },
        { $project: { name: 1, email: 1 } },
      ],
      as: "customer",
    },
  },
]);

The $project inside the sub-pipeline matters too - pulling back only the two fields you actually need instead of the entire customer document keeps the memory footprint of the join proportional to what you'll use, not to how large the customer schema happens to be.

Step 5 - know when $group needs its own index-friendly $match first

$group can't use an index directly, since it's building new documents rather than reading existing ones - but everything before it in the pipeline can. Push as much filtering as possible ahead of the $group stage so it's aggregating over the smallest set of documents that could possibly qualify, rather than grouping the whole collection and discarding groups afterward.

The pattern underneath all of this

Nearly every slow aggregation pipeline in production traces back to the same root cause: a stage doing more work than it needs to, either because it runs before a filter that would have shrunk its input, or because the field it's filtering or sorting on doesn't have a matching index. explain() will always point you at exactly which stage that is - the fix from there is almost always reordering or indexing, rarely rewriting the pipeline's logic from scratch.