You needed a join, so you reached for $lookup:
db.orders.aggregate([
{ $lookup: { from: "customers", localField: "customerId", foreignField: "_id", as: "customer" } },
{ $unwind: "$customer" },
{ $match: { "customer.country": "DE", status: "shipped" } },
{ $sort: { createdAt: -1 } },
{ $limit: 20 }
])
With 5 million orders, it takes a minute and a half. MongoDB can join efficiently, but $lookup works very differently from a SQL join optimizer, and the order of stages matters a lot.
How $lookup executes
For each input document, $lookup runs a query against the from collection. It's a nested-loop join. There's no hash join over the whole collection (outside some newer, limited cases), and the planner won't reorder your stages for you across the lookup. So the cost is roughly:
(number of documents reaching $lookup) × (cost of one lookup query)
The pipeline above joins all 5 million orders to customers, then filters, sorts and keeps 20.
Fix 1: shrink the input before $lookup
Filter, sort and limit on the local collection first, using its indexes:
db.orders.([
{ : { : } },
{ : { : - } },
{ : },
{ : { : , : , : , : } },
{ : },
{ : { : } },
{ : }
])
