Why Your NestJS Aggregation Pipeline Is Slow (It's Not Your Indexes)

Add an index, watch the query speed up, move on. That's the instinct most of us have when a MongoDB aggregation starts crawling. It works right up until your orders collection crosses a few hundred thousand documents and the same pipeline that ran fine in staging starts throwing QueryExceededMemoryLimitNoDiskUseAllowed in production.

I've seen this exact failure play out in NestJS backends more times than indexing gaps alone. The real problem is usually stage order — where $lookup, $project, and $match sit relative to each other in the pipeline array.

The stage order is the index

MongoDB can only apply an index to an aggregation if the pipeline opens with $match (and, on the same compound prefix, $sort). Put a $lookup or $project first and you've thrown that away before the engine gets a chance to use it.

Here's the pattern that quietly wrecks performance:

// BAD: $lookup and $project execute before filtering, scanning the entire collection
const badPipeline = [
  { $lookup: { from: 'users', localField: 'userId', foreignField: '_id', as: 'user' } },
  { $project: { orderId: 1, total: 1, 'user.email': 1 } },
  { $match: { status: 'COMPLETED', createdAt: { $gte: startDate } } }, // scans everything first
];

The join and the projection both run against the full collection, and the filter only trims the result afterward. Flip the order and the same query becomes index-eligible:

// GOOD: filter using compound index { status: 1, createdAt: -1 } before touching anything else
const optimizedPipeline = [
  { $match: { status: 'COMPLETED', createdAt: { $gte: startDate } } },
  { $sort: { createdAt: -1 } },
  { $limit: 50 }, // shrink the working set before the expensive stage
  { $lookup: { from: 'users', localField: 'userId', foreignField: '_id', as: 'user' } },
  { $project: { orderId: 1, total: 1, user: { $arrayElemAt: ['$user.email', 0] } } },
];

Same result set, completely different execution cost. $limit before $lookup matters here too — you're capping how many documents the join even has to touch.

Stop letting $lookup scan the whole related collection

The default $lookup syntax joins on localField/foreignField and pulls back every matching document, unfiltered. On a transactions collection with millions of rows, that's expensive even with an index on the join key.

The fix is the correlated subquery form — let plus an inner pipeline — which lets you filter and project the joined collection before it comes back:

// src/orders/orders-aggregation.service.ts
import { Injectable } from '@nestjs/common';
import { InjectModel } from '@nestjs/mongoose';
import { Model } from 'mongoose';

@Injectable()
export class OrdersAggregationService {
  constructor(@InjectModel('Order') private readonly orderModel: Model<OrderDocument>) {}

  async getCustomerHighValueAnalytics(organizationId: string) {
    return await this.orderModel.aggregate([
      // 1. Filter root collection using index: { orgId: 1, totalAmount: -1 }
      { $match: { orgId: organizationId, totalAmount: { $gt: 1000 } } },

      // 2. Correlated subquery lookup with indexed inner filter
      {
        $lookup: {
          from: 'transactions',
          let: { orderId: '$_id' },
          pipeline: [
            // requires index on transactions: { orderId: 1, isSuccessful: 1 }
            {
              $match: {
                $expr: {
                  $and: [
                    { $eq: ['$orderId', '$$orderId'] },
                    { $eq: ['$isSuccessful', true] },
                  ],
                },
              },
            },
            { $project: { transactionId: 1, paymentMethod: 1, settledAt: 1 } },
          ],
          as: 'transactions',
        },
      },

      // 3. Group and sort with a disk-spill safety net
      {
        $group: {
          _id: '$customerId',
          totalSpent: { $sum: '$totalAmount' },
          orderCount: { $sum: 1 },
        },
      },
      { $sort: { totalSpent: -1 } },
      { $limit: 20 },
    ], {
      allowDiskUse: true, // let $group spill to disk instead of throwing past 100MB
      maxTimeMS: 5000,    // fail fast instead of hanging a request thread
    });
  }
}

Most tutorials stop at the basic $lookup syntax and never mention the let/pipeline form, which is a shame — it's the difference between a join that scans everything and one that only ever touches matching rows. If you're joining against anything larger than a lookup table, this is worth the extra few lines every time.

Don't guess — check for IXSCAN

Don't assume an index is being used just because one exists on the collection. Run .explain() and check:

const executionPlan = await orderModel.aggregate(pipeline).explain('executionStats');
console.log(JSON.stringify(executionPlan.stages[0].$cursor.executionStats, null, 2));

Two things to look for in the output:

• stage: "IXSCAN" — the query used an index. COLLSCAN means it didn't, no matter how confident you were that it would.
• totalDocsExamined close to nReturned — if MongoDB examined 500,000 documents to return 50, your filter isn't as selective as you think, or it's running at the wrong stage.

I'd make this a habit on any aggregation touching a collection over ~50k documents, not just the ones that are already slow. It's cheap insurance.

Quick reference

None of this requires exotic tooling — it's stage ordering, one alternate $lookup syntax, and the discipline to run .explain() instead of assuming. Get those three right and sub-25ms responses on multi-collection aggregations are a realistic target, not a lucky outcome.