Optimizing MongoDB Aggregation: 30% Faster Multi-Collection Queries

A MongoDB aggregation pipeline is a program, and like any program it can be written to run in milliseconds or to crawl. The same logical result — a multi-collection rollup — can vary by orders of magnitude depending on stage order and index usage. On a clinical workflow API, tightening these pipelines was a large part of a 30% query-efficiency gain.

Filter early, filter on indexes

The single most important rule: put $match first, and match on indexed fields. Every document you eliminate at stage one is a document the rest of the pipeline never touches. A pipeline that sorts or joins before filtering is doing expensive work on rows it is about to throw away — the database equivalent of cleaning a house before deciding to demolish it.

// $match first, on an indexed field — shrink the set early
const rows = await Visits.aggregate([
  { $match: { clinicId, status: "active" } },   // indexed, runs first
  { $sort: { scheduledAt: -1 } },               // on the small set
  { $lookup: { from: "patients", localField: "patientId",
               foreignField: "_id", as: "patient" } },
  { $limit: 50 },
]);

$lookup is a join — treat it like one

$lookup is the most expensive stage in most pipelines because it is a join, and joins multiply work. Run it as late as possible — after matching and limiting — so you join against dozens of documents, not thousands. And ensure the foreign field is indexed, or each lookup degrades into a collection scan repeated once per input document.

Profile with explain

Guesswork has no place here. Run the pipeline through explain("executionStats") and read what actually happened: which stages used an index, how many documents each examined versus returned, and where the time went. The 30% gain came from reading those numbers and reordering stages — not from intuition about what "should" be fast.

An aggregation pipeline is read top to bottom but should be designed bottom-up: decide the smallest set you can get away with, then make sure every stage operates only on it.

The production system is the Hospital-API; for the vector side of querying, see Vector Embeddings in Production.