The aggregation pipeline

The model

Ordered stages; each transforms a document stream and feeds the next (like Unix pipes).

A stage may add/drop/reshape docs. Read-only unless it ends in $out/$merge.

Stages = SQL clauses

Optimizer rewrites (free)

$match moved early — before $sort (less to sort) and before a projection (can hit an index) = predicate pushdown.

$sort + $limit coalesced → top-N, keep only N in memory.

Early $project to drop fields = unneeded; done automatically.

$lookup — the join to avoid

Left outer join to another collection (same DB); adds an array field of matched foreign docs.

Manual: "Excessive use of $lookup may slow down query performance… consider an embedded data model."

Heavy $lookup = your schema wants embedding (back to 0001).

Do-this

Put $match (and a usable $sort) first so the opening stage uses an index — the first-stage index use you read in explain() (0004).

Example

[ {$match}, {$group:{_id:"$city",n:{$sum:1}}}, {$sort:{n:-1}}, {$limit:5} ]

= active users per city, busiest 5 first.