Indexing & the Aggregation Framework

Two halves, one collection

Lecture 13 gave you find() and the query operators. Every query you wrote worked. None of them were fast — the collection was simply small enough to hide it.

A scan reads everything. A B-tree does not.

With no usable index, db.rides.find({ "route.pickup": "Connaught Place" }) reads all 15,000 documents to return ~1,139. Plan stage: COLLSCAN — and it is O(n), with no early exit.

createIndex() — and when direction matters

db.rides.createIndex({ status: 1 })                 // 1 = ascending
db.rides.createIndex({ booking_value: -1 })         // -1 = descending
db.rides.createIndex({ "route.pickup": 1 }, { name: "pickup_idx" })

db.rides.getIndexes()
db.rides.dropIndex("pickup_idx")

For a single-field index, direction is almost irrelevant — a B-tree walks either way. It becomes decisive for a compound sort, which an index serves only if the sort matches it exactly, or is its exact mirror.

db.rides.createIndex({ vehicle_type: 1, booking_value: -1 })

.sort({ vehicle_type:  1, booking_value: -1 })   // ✅ exact match
.sort({ vehicle_type: -1, booking_value:  1 })   // ✅ exact mirror
.sort({ vehicle_type:  1, booking_value:  1 })   // ❌ in-memory SORT

Five index types worth knowing by name

Also: unique (natural for booking_id), partial — ideal here, since booking_value is absent on ~5,000 cancelled bookings — and 2dsphere for "drivers within 3 km".

The prefix rule, then ESR

Given { status: 1, vehicle_type: 1, booking_value: -1 }, the index serves any query using a left prefix of its fields:

"Completed Go Sedan rides over ₹500, newest first"

db.rides.find({
  status: "Completed",          // E — equality
  vehicle_type: "Go Sedan",     // E — equality
  booking_value: { $gt: 500 }   // R — range
}).sort({ date: -1 })           // S — sort

db.rides.createIndex({ status: 1, vehicle_type: 1, date: -1, booking_value: 1 })
//                     └── E ──┘  └──── E ─────┘  └─ S ─┘  └───── R ─────┘

explain("executionStats") — four numbers

db.rides.find({ "route.pickup": "Connaught Place" })
        .explain("executionStats")

Three tiers of query efficiency

stage: "COLLSCAN"
nReturned: 1139
totalDocsExamined: 15000
executionTimeMillis: 28

// createIndex({"route.pickup":1})
stage: "IXSCAN"
nReturned: 1139
totalDocsExamined: 1139
executionTimeMillis: 3

stage: "PROJECTION_COVERED"
nReturned: 1763
totalDocsExamined: 0

A covered query is answered from the index alone — every filtered and returned field lives in the index, so the documents are never touched. Note that _id must be explicitly excluded:

db.rides.createIndex({ status: 1, vehicle_type: 1, booking_value: 1 })
db.rides.find(
  { status: "Completed", vehicle_type: "Go Sedan" },
  { _id: 0, vehicle_type: 1, booking_value: 1 }
)

Indexes are not free — more is not better

db.rides.aggregate([{ $indexStats: {} }])
// accesses.ops: 0 → drop candidate

Atlas Performance Advisor (Clusters → Performance Advisor) profiles real traffic and ranks index recommendations by expected impact, flagging unused and redundant ones too.

A pipeline is a conveyor belt of stages

db.rides.aggregate([
  { $match: { status: "Completed" } },                  // 15,000 → 9,343
  { $group: { _id: "$vehicle_type",
              revenue: { $sum: "$booking_value" } } },  //  9,343 → 7
  { $sort:  { revenue: -1 } },                          //      7 → 7, ordered
  { $limit: 3 }                                         //      7 → 3
])
// → Premier Sedan ₹5,36,855 · Go Sedan ₹4,91,473 · Uber XL ₹4,31,927

The stages and accumulators you need

$match and $limit as early as possible

db.rides.aggregate([
  { $group: { _id: "$vehicle_type",
      revenue: { $sum: "$booking_value" }}},
  { $match: { _id: "Go Sedan" } }
])
// groups all 15,000, discards most

db.rides.aggregate([
  { $match: { vehicle_type: "Go Sedan" } },
  { $group: { _id: "$vehicle_type",
      revenue: { $sum: "$booking_value" }}}
])
// groups 2,854 — a fifth of the work

Fewer documents for every later stage is merely arithmetic. The decisive reason is different: only a $match in the first position can use an index. Once any stage has transformed the stream, the documents are no longer the indexed documents on disk — every later $match is an in-memory filter over intermediate results.

Same logic for $sort: leading $sort → index-served. After $group → in-memory, against a hard 100 MB stage limit. $sort + $limit adjacent gets a special top-n optimisation.

Two findings that are usefully negative

// Q1 — completion rate by vehicle type. $cond inside $sum gives you
// numerator and denominator in a single pass over the data.
db.rides.aggregate([
  { $group: {
      _id: "$vehicle_type",
      total:     { $sum: 1 },
      completed: { $sum: { $cond: [{ $eq: ["$status", "Completed"] }, 1, 0] } },
      revenue:   { $sum: "$booking_value" }
  }},
  { $addFields: { completion_rate_pct: { $round: [
      { $multiply: [{ $divide: ["$completed", "$total"] }, 100] }, 2 ]}}},
  { $sort: { revenue: -1 } }
])

Q6 — average driver rating by vehicle, completed rides only ($match on status + "ratings.driver": { $exists: true }) — lands every type between 4.39 and 4.42, against a fleet average of 4.41.

Dates are strings here — and that is the lesson

booked_at ("2024-03-14T18:22:07Z") and date ("2024-03-14") are strings, not BSON Dates. ISO strings sort lexicographically in date order, so substrings work — or convert properly.

// Q2 — revenue by month
db.rides.aggregate([
  { $match: { status: "Completed" } },
  { $group: { _id: { $substr: ["$date", 0, 7] },        // "2024-03"
              revenue: { $sum: "$booking_value" },
              rides:   { $sum: 1 } }},
  { $sort: { _id: 1 } }
])
// → Mar strongest ₹1,99,972 / 859 rides · Jun weakest ₹1,66,260
//   twelve months sum to the ₹22,02,449 of completed value

// Q3 — busiest hour of day
db.rides.aggregate([
  { $group: { _id: { $toInt: { $substr: ["$booked_at", 11, 2] } },
              bookings: { $sum: 1 } }},
  { $sort: { bookings: -1 } }, { $limit: 5 }
])

// The convert-first alternative:
{ $hour: { $dateFromString: { dateString: "$booked_at" } } }
{ $dateToString: { format: "%Y-%m",
    date: { $dateFromString: { dateString: "$booked_at" } } } }

Demand is bimodal — and supply arrives late

{ $match: { bookings: { $gte: 1000 } } }
// ← guard, after $group, before ranking

Where and why bookings fail

// Q4 — cancellation rate by pickup. $match AFTER $group = SQL's HAVING:
// a rate on a thin sample is noise, so guard with a volume threshold.
db.rides.aggregate([
  { $group: {
      _id: "$route.pickup",
      bookings:  { $sum: 1 },
      cancelled: { $sum: { $cond: [{ $in: ["$status",
          ["Cancelled by Driver","Cancelled by Customer","No Driver Found"]] }, 1, 0] }}
  }},
  { $match: { bookings: { $gte: 1000 } } },
  { $addFields: { cancellation_rate_pct: { $round: [
      { $multiply: [{ $divide: ["$cancelled","$bookings"] }, 100] }, 2 ]}}},
  { $sort: { cancellation_rate_pct: -1 } }, { $limit: 10 }
])

Every KPI in one pass, then materialise it

db.rides.aggregate([{ $facet: {
  headline: [
    { $group: { _id: null,                       // null = one grand total
        total_bookings: { $sum: 1 },
        completed: { $sum: { $cond: [{ $eq: ["$status","Completed"] }, 1, 0] }},
        completed_value: { $sum: "$booking_value" },
        avg_distance_km: { $avg: "$ride_distance" } }}
  ],
  by_status:   [ { $group: { _id: "$status", n: { $sum: 1 } } },
                 { $sort: { n: -1 } } ],
  payment_mix: [ { $match: { payment_method: { $exists: true } } },
                 { $group: { _id: "$payment_method", n: { $sum: 1 } } } ],
  fare_bands:  [ { $match: { booking_value: { $exists: true } } },
                 { $bucket: { groupBy: "$booking_value",
                     boundaries: [0,100,250,500,1000], default: "1000+",
                     output: { rides: { $sum: 1 } } } } ]
}}])
// headline → 15,000 bookings · 9,343 completed · 62.29% · ₹22,02,449 · 10.87 km
// payment_mix → UPI 45.03% · Cash 25.31% · Wallet 11.79% · Credit 10.79% · Debit 7.08%

Then write the answer down once, so the dashboard reads results instead of raw data:

{ $out: "monthly_revenue" }                              // full rebuild, destructive
{ $merge: { into: "monthly_revenue", on: "_id",
            whenMatched: "replace", whenNotMatched: "insert" } }   // incremental, safe

Six things to carry forward

1. An unindexed query is O(n). COLLSCAN reads everything; a B-tree IXSCAN is ~O(log n), so growth stops mattering.
2. Compound field order is decided by ESR — Equality, Sort, Range — because a range selects a span, and nothing stays sorted behind a span.
3. Judge a query by totalDocsExamined ÷ nReturned, not milliseconds. 1:1 is perfect; a covered query examines zero documents.
4. Indexes are not free. Every write pays for every index and they compete with documents for RAM. Index the queries you run; drop what $indexStats shows unused.
5. A pipeline is an ordered conveyor belt that maps almost one-to-one onto SQL: $match≈WHERE, $group≈GROUP BY, $sort≈ORDER BY, $project≈SELECT, $lookup≈LEFT JOIN.
6. $match and $limit go first — later stages then do less work, and only a leading $match can use an index. Materialise with $out/$merge.