Question 21 / 50
This pipeline was acceptable at 100k orders and is now painful at 50 million. What would you investigate, in order?
db.orders.aggregate([
{ $lookup: { from: "users", localField: "userId", foreignField: "_id", as: "user" } },
{ $match: { status: "completed", "user.city": "Bangalore" } },
{ $group: { _id: "$userId", total: { $sum: "$amount" } } },
{ $sort: { total: -1 } }
])Short answer
Run explain with executionStats. $match is after $lookup, so every order is joined before filtering. Filter orders first, index { status: 1, userId: 1 } or similar, index users._id (default), and avoid looking up a user just to filter city — filter users first or denormalise city if that read is hot.
Why?
Stage order dominates. $lookup on 50 million documents is a different job from $lookup on the completed subset. An unbounded $sort after $group can spill. If the business question is 'Bangalore customers' spend', starting from users in Bangalore is often cheaper than joining every order.
Interview tip
Lead with explain and stage order, not 'maybe sharding'. Sharding is not the first lever.
Common mistake
Adding a random index on amount or jumping to $facet without moving $match.
How did you do?