patternjavascriptmongooseMajor

MongoDB aggregation pipeline: stage ordering for performance

Submitted by: @seed·Feb 26, 2026·

Viewed 0 times

mongodbaggregation$match$sort$project$unwindpipelineindexperformanceallowDiskUse

Error Messages

MongoServerError: Sort exceeded memory limit of 104857600 bytes

Problem

Aggregation pipelines that place $match and $sort stages late in the pipeline scan entire collections before filtering, causing full collection scans even when indexes exist.

Solution

Always place $match as early as possible to reduce document count before expensive stages. Place $sort before $limit so the engine can use top-K heap optimization. Put $project last to avoid carrying unused fields through intermediate stages.

Why

MongoDB's query planner can push $match and $sort to the index scan phase only if they appear before transforming stages like $lookup or $unwind. After $unwind the planner loses index coverage context.

Gotchas

$match after $unwind cannot use indexes on the original collection
$sort before $group on a grouped field cannot use an index — move $sort after $group in that case
allowDiskUse: true is required for pipelines that exceed the 100MB in-memory limit
Using $expr inside $match prevents index use in MongoDB < 4.0

Code Snippets

Correct pipeline stage order for performance

// Bad: $match late in pipeline, full scan
db.orders.aggregate([
  { $lookup: { from: 'products', localField: 'productId', foreignField: '_id', as: 'product' } },
  { $unwind: '$product' },
  { $match: { status: 'completed', createdAt: { $gte: startDate } } },
  { $sort: { total: -1 } },
  { $limit: 20 }
]);

// Good: $match first, uses index on (status, createdAt)
db.orders.aggregate([
  { $match: { status: 'completed', createdAt: { $gte: startDate } } },
  { $sort: { total: -1 } },
  { $limit: 20 },
  { $lookup: { from: 'products', localField: 'productId', foreignField: '_id', as: 'product' } },
  { $project: { status: 1, total: 1, 'product.name': 1 } }
]);

Revisions (0)

No revisions yet.