Indexing & the Aggregation Framework

Making queries fast, and turning 15,000 raw bookings into the handful of numbers a management meeting actually needs

Learning Objectives

• ✓ Explain why an unindexed query is O(n) and quantify what that costs as a collection grows from thousands to millions of documents
• ✓ Create single-field, compound, multikey, text and TTL indexes, and choose the right type for a given access pattern
• ✓ Apply the ESR rule — Equality, Sort, Range — to order the fields of a compound index correctly
• ✓ Read explain("executionStats") and judge a query by the ratio of totalDocsExamined to nReturned
• ✓ Articulate the cost side of indexing — RAM, write amplification, the working set — and argue against over-indexing
• ✓ Build multi-stage aggregation pipelines using $match, $group, $project, $unwind, $lookup and $facet
• ✓ Answer seven real business questions about the rides collection, guard rate-based rankings with a minimum-volume $match, and materialise the answers with $merge for Lecture 15

Session Timeline

Section 1: The Cost of a Collection Scan

Every query you wrote in Lecture 13 worked. None of them were fast — they simply ran against a collection small enough that slowness was invisible. That is the most dangerous state a system can be in, because the problem is not absent, it is merely waiting for growth.

When MongoDB has no index that can help, it performs a collection scan, reported in query plans as COLLSCAN. It reads every document in the collection, one by one, and tests each against your filter. There is no shortcut, no early exit — even if the single matching document happens to be the first one read, the engine cannot know that until it has checked the last one.

// This reads all 15,000 documents to return roughly 1,139 of them
db.rides.find({ "route.pickup": "Connaught Place" })

Why This Is O(n), and Why That Matters Commercially

The work done by a collection scan is directly proportional to the number of documents. Double the collection, double the work. There is no efficiency of scale; there is only linear cost. Put numbers against it:

And the cost is not only time. A collection scan pulls every document off disk and through RAM, evicting genuinely useful data from the cache. One badly planned analytics query can therefore slow down the unrelated transactional queries running beside it. A single scan is rarely the whole problem; a scan running fifty times a minute on a live cluster is.

The Index Alternative

An index is a separate, ordered data structure — MongoDB uses a B-tree — that stores the values of one or more fields together with pointers to the documents holding them. Because it is sorted, the engine can navigate it the way you use a book index: jump to the right region, read only the matching entries, and follow the pointers to exactly those documents.

The B-tree turns the search from O(n) into roughly O(log n). The practical consequence of a logarithm is worth stating plainly: finding a document among 15 million costs only about twice as much work as finding one among 15 thousand. The query plan changes from COLLSCAN to IXSCAN, and the cost of growth all but disappears.

Section 2: Creating Indexes — Syntax and Types

Indexes are created on a collection with createIndex(). The argument is a document of field-to-direction pairs, where 1 means ascending and -1 descending.

// Single-field, ascending
db.rides.createIndex({ status: 1 })

// Descending — useful when the dominant sort is "largest first"
db.rides.createIndex({ booking_value: -1 })

// Named explicitly, and built without blocking other operations
db.rides.createIndex(
  { "route.pickup": 1 },
  { name: "pickup_idx" }
)

// Inspect and remove
db.rides.getIndexes()
db.rides.dropIndex("pickup_idx")

Note the dotted path: "route.pickup" indexes a field inside an embedded document exactly as if it were top level. Quotation marks are required because of the dot.

When Does Direction Actually Matter?

For a single-field index, almost never. A B-tree can be walked in either direction, so an ascending index on booking_value serves sort({ booking_value: -1 }) perfectly well.

Direction becomes decisive the moment a compound index must support a multi-field sort. The index can only serve a sort if the requested sort matches the index order either exactly, or exactly inverted.

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

// SERVED — matches the index exactly
db.rides.find().sort({ vehicle_type: 1, booking_value: -1 })

// SERVED — the exact mirror image of the index
db.rides.find().sort({ vehicle_type: -1, booking_value: 1 })

// NOT SERVED — neither the same nor the mirror; MongoDB must sort in memory
db.rides.find().sort({ vehicle_type: 1, booking_value: 1 })

The Five Index Types You Will Use

Multikey indexes

You do not ask for a multikey index; MongoDB creates one automatically when the indexed field contains an array. It stores a separate index entry for every element, which is what makes find({ tags: "peak" }) fast. The cost follows directly: a document with twenty array elements writes twenty index entries. Indexing a large array is an expensive decision, and only one array field may appear in a single compound index.

Text indexes

db.rides.createIndex({ "cancellation.reason": "text" })

db.rides.find({ $text: { $search: "breakdown vehicle" } })

A text index tokenises, lowercases and stems the content, so "cancelling" matches "cancel". A collection may have only one text index, though it can cover several fields. For anything resembling real search — typo tolerance, relevance tuning, faceting — Atlas Search (built on Lucene) is the production answer; the basic text index is best thought of as keyword matching.

TTL indexes

// Delete documents 24 hours after their createdAt timestamp
db.driver_pings.createIndex(
  { createdAt: 1 },
  { expireAfterSeconds: 86400 }
)

A background process sweeps roughly every 60 seconds and removes expired documents. Two constraints matter: the indexed field must hold a real BSON Date (a string will be silently ignored), and deletion is approximate, not instantaneous. TTL is the cleanest way to meet a data-retention policy — "we do not keep raw location data beyond 30 days" becomes one line of configuration rather than a cron job nobody maintains.

// A partial index: skip the ~5,000 documents with no booking_value at all
db.rides.createIndex(
  { booking_value: -1 },
  { partialFilterExpression: { booking_value: { $exists: true } } }
)

Section 3: Compound Indexes and the ESR Rule

A compound index covers up to 32 fields, and the order of those fields is the single most consequential decision in MongoDB performance tuning. The same three fields in a different order can be the difference between examining 400 documents and examining 15,000.

The Prefix Rule

A compound index can serve any query that uses a left-prefix of its fields. Given { status: 1, vehicle_type: 1, booking_value: -1 }:

This is why one well-ordered compound index often replaces three single-field indexes: it serves the full query and every prefix of it. Fewer indexes, less RAM, cheaper writes.

The ESR Rule

The reasoning is geometric. An equality match on the leading field narrows the B-tree to one contiguous block of entries. Inside that block the entries are still ordered by the next field, so a sort on it is free — the engine simply walks the block. A range, by contrast, selects a span of entries rather than a point, so anything after a range field is no longer in a single sorted run and must be re-sorted in memory. Put the range last and nothing is left behind it to spoil.

Worked Example on rides

The business question: "Show me completed Go Sedan rides worth more than ₹500, most recent 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

Apply ESR mechanically: two equality fields, then the sort field, then the range field.

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

Section 4: Reading explain("executionStats")

Appending .explain("executionStats") to a query makes MongoDB run it and report exactly what it did. This is the single most valuable diagnostic skill in the module, and it is entirely learnable in ten minutes.

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

The Four Numbers That Matter

Before and After on Our Collection

// BEFORE — no index on route.pickup
{
  stage: "COLLSCAN",
  nReturned: 1139,
  totalDocsExamined: 15000,   // ratio ≈ 13 : 1
  executionTimeMillis: 28
}

db.rides.createIndex({ "route.pickup": 1 })

// AFTER
{
  stage: "IXSCAN",
  nReturned: 1139,
  totalKeysExamined: 1139,
  totalDocsExamined: 1139,    // ratio = 1 : 1
  executionTimeMillis: 3
}

Note that even the "bad" version returns in 28 ms on 15,000 documents. Scale the collection a thousandfold and the indexed query stays in single-digit milliseconds while the scan moves into tens of seconds. You are not tuning today's performance; you are buying immunity to growth.

Covered Queries

There is a tier above a 1:1 ratio. If every field the query needs — both the filtered fields and the returned fields — lives inside the index, MongoDB never touches the documents at all. It answers entirely from the index. This is a covered query, and totalDocsExamined is 0.

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

// Covered: filter and projection both live entirely in the index.
// _id must be explicitly excluded, because _id is not in this index.
db.rides.find(
  { status: "Completed", vehicle_type: "Go Sedan" },
  { _id: 0, vehicle_type: 1, booking_value: 1 }
).explain("executionStats")

// stage: "PROJECTION_COVERED"
// totalDocsExamined: 0        ← the documents were never read

Section 5: The Price of an Index

If indexes only made things faster, the correct strategy would be to index everything. Three costs make that strategy wrong, and a manager who can name them will not approve a bad one.

Cost 1 — Every Write Pays for Every Index

An insert writes one document plus one entry in every index on the collection. An update that changes an indexed field must remove and reinsert the corresponding entry, in sorted position. A collection with twelve indexes does roughly thirteen units of write work per insert instead of one.

For a ride-hailing platform ingesting bookings continuously, this is not theoretical. Indexes added for the analytics team are paid for, permanently, by the operational write path.

Cost 2 — Indexes Consume RAM, and RAM Is the Whole Game

Your working set is the data and indexes your queries touch regularly. MongoDB's performance profile is essentially binary: if the working set fits in RAM, queries are fast; if it does not, the engine starts reading from disk and performance falls off a cliff — frequently by a factor of 50 or more.

Cost 3 — Unused Indexes Are Pure Overhead

An index that no query uses still costs write time and still occupies RAM. It contributes nothing. MongoDB tracks usage, so this is measurable rather than a matter of opinion:

db.rides.aggregate([{ $indexStats: {} }])
// Each entry reports accesses.ops — the number of operations
// that have used that index since the server last started.
// A long-lived server showing ops: 0 is a drop candidate.

A Workable Indexing Policy

Atlas Performance Advisor — the Practical Tool

You do not have to find slow queries by intuition. Atlas continuously profiles real traffic and, under Clusters → Performance Advisor, recommends specific indexes ranked by expected impact, showing the queries that triggered each recommendation. It also flags indexes that appear unused or redundant.

Section 6: The Aggregation Pipeline Mental Model

Indexing makes retrieval fast. It does not answer a management question. "Which pickup point loses us the most revenue to cancellations?" is not a find() — it requires grouping, computing and ranking. That is the aggregation framework.

The Conveyor Belt

A pipeline is an ordered array of stages. Documents enter at the top, each stage transforms the stream, and the output of one stage is the input to the next. A stage may filter documents out, reshape them, combine many into one, or split one into many.

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
])

Read it top to bottom as a sentence: keep only completed rides, group them by vehicle type summing booking value, order by revenue descending, keep the top three. That returns Premier Sedan ₹5,36,855, Go Sedan ₹4,91,473 and Uber XL ₹4,31,927.

The SQL Mapping

You already know this material in another notation. The translation is close to exact.

The Stage Vocabulary

Accumulators

Accumulators are the expressions used inside $group to reduce many documents to one value.

Working With Our String Dates

In rides, booked_at ("2024-03-14T18:22:07Z") and date ("2024-03-14") are strings, not BSON Date values. This mirrors a great deal of real imported data, and gives us three options:

// 1. Substring extraction — fast, no conversion, works because ISO strings
//    sort lexicographically in the same order as the dates they represent
{ $substr: ["$date", 0, 7] }                       // "2024-03"
{ $toInt: { $substr: ["$booked_at", 11, 2] } }     // 18

// 2. Convert, then use the real date operators
{ $dateFromString: { dateString: "$booked_at" } }  // or the shorthand $toDate
{ $hour:  { $dateFromString: { dateString: "$booked_at" } } }
{ $month: { $dateFromString: { dateString: "$booked_at" } } }
{ $year:  { $dateFromString: { dateString: "$booked_at" } } }

// 3. Convert and re-format for a human-readable label
{ $dateToString: {
    format: "%Y-%m",
    date: { $dateFromString: { dateString: "$booked_at" } }
} }                                                // "2024-03"

Section 7: Seven Business Questions, Seven Pipelines

Every pipeline below runs against bdma.rides and produces the stated answer. Type them; do not paste them.

1. What is the completion rate by vehicle type?

The pattern to learn here is $cond inside $sum — a conditional count that lets you compute a numerator and a 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 } }
])

2. What is revenue by month?

db.rides.aggregate([
  { $match: { status: "Completed" } },
  { $group: {
      _id: { $substr: ["$date", 0, 7] },     // "2024-03"
      revenue: { $sum: "$booking_value" },
      rides:   { $sum: 1 },
      avg_fare: { $avg: "$booking_value" }
  }},
  { $project: {
      _id: 0,
      month: "$_id",
      rides: 1,
      revenue: { $round: ["$revenue", 0] },
      avg_fare: { $round: ["$avg_fare", 2] }
  }},
  { $sort: { month: 1 } }
])

March is the strongest month at ₹1,99,972 across 859 completed rides; June is the weakest at ₹1,66,260. The twelve months sum to the ₹22,02,449 of completed booking value.

The $dateFromString version produces the same grouping and is what you would write if the field could hold mixed formats:

{ $group: {
    _id: { $dateToString: {
      format: "%Y-%m",
      date: { $dateFromString: { dateString: "$booked_at" } }
    }},
    revenue: { $sum: "$booking_value" }
}}

3. Which hour of the day is busiest?

db.rides.aggregate([
  { $group: {
      _id: { $toInt: { $substr: ["$booked_at", 11, 2] } },
      bookings:  { $sum: 1 },
      completed: {
        $sum: { $cond: [{ $eq: ["$status", "Completed"] }, 1, 0] }
      }
  }},
  { $addFields: {
      failure_rate_pct: {
        $round: [{ $multiply: [
          { $divide: [{ $subtract: ["$bookings", "$completed"] }, "$bookings"] }, 100
        ]}, 2]
      }
  }},
  { $sort: { bookings: -1 } },
  { $limit: 5 }
])

Now Re-Rank by Rate — and Watch the Answer Fall Apart

The pipeline above ranks by volume. Change one line to rank by rate instead, and the result stops being useful:

  { $sort: { failure_rate_pct: -1 } },
  { $limit: 5 }

  { $match: { bookings: { $gte: 1000 } } },   // ← guard before ranking
  { $sort:  { failure_rate_pct: -1 } }

Five hours clear 1,000 bookings — 08:00, 09:00, 17:00, 18:00 and 19:00 — and 17:00 leads them at 39.92%. That is the number to take to an operations meeting, and the guard is what makes it defensible.

4. Which pickup points have the worst cancellation rates?

The same trap as 03:00, in a different dimension — so apply the same guard. Rank only pickup points carrying real volume, using a $match after $group, which is SQL's HAVING.

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
        ]}
      },
      revenue: { $sum: "$booking_value" }
  }},
  { $match: { bookings: { $gte: 1000 } } },        // HAVING — ignore thin samples
  { $addFields: {
      cancellation_rate_pct: {
        $round: [{ $multiply: [{ $divide: ["$cancelled", "$bookings"] }, 100] }, 2]
      }
  }},
  { $sort: { cancellation_rate_pct: -1 } },
  { $limit: 10 }
])

Only five pickup points clear the 1,000-booking threshold, so $limit: 10 returns five rows. The spread is narrow — 31.24% to 33.50% — which again says the cause is systemic rather than local. Note the definition baked into the $in array: "cancelled" here means driver cancellation, customer cancellation, or no driver found. Write the definition into the pipeline and state it beside the number, because a rate is meaningless until its numerator is defined.

5. Why are rides cancelled, and by whom?

A two-level grouping key. Set _id to an object and every distinct combination of its fields becomes a group.

db.rides.aggregate([
  { $match: { "cancellation.by": { $exists: true } } },
  { $group: {
      _id: { by: "$cancellation.by", reason: "$cancellation.reason" },
      count: { $sum: 1 }
  }},
  { $group: {
      _id: "$_id.by",
      total: { $sum: "$count" },
      reasons: { $push: { reason: "$_id.reason", count: "$count" } }
  }},
  { $unwind: "$reasons" },
  { $project: {
      _id: 0,
      cancelled_by: "$_id",
      reason: "$reasons.reason",
      count: "$reasons.count",
      share_pct: {
        $round: [{ $multiply: [{ $divide: ["$reasons.count", "$total"] }, 100] }, 2]
      }
  }},
  { $sort: { cancelled_by: 1, share_pct: -1 } }
])

This pipeline teaches three techniques at once: $group on a composite _id, a second $group that rolls the first one up to get a per-side total, and $push plus $unwind to carry detail rows through a grouping and then flatten them again. That group-then-regroup-then-unwind idiom is how you express "percentage of its own subtotal".

6. Does driver rating vary by vehicle type?

Ratings exist only on completed rides, so the $match is mandatory rather than cosmetic. The $exists guard makes the intent explicit and survives any dirty record.

db.rides.aggregate([
  { $match: {
      status: "Completed",
      "ratings.driver": { $exists: true }
  }},
  { $group: {
      _id: "$vehicle_type",
      rides: { $sum: 1 },
      avg_driver_rating:   { $avg: "$ratings.driver" },
      avg_customer_rating: { $avg: "$ratings.customer" },
      worst: { $min: "$ratings.driver" },
      best:  { $max: "$ratings.driver" }
  }},
  { $project: {
      _id: 0,
      vehicle_type: "$_id",
      rides: 1,
      avg_driver_rating:   { $round: ["$avg_driver_rating", 2] },
      avg_customer_rating: { $round: ["$avg_customer_rating", 2] },
      worst: 1, best: 1
  }},
  { $sort: { avg_driver_rating: -1 } }
])

Every vehicle type lands between 4.39 and 4.42, against a fleet average driver rating of 4.41 and a customer rating of 4.38. The honest analytical conclusion is a negative one: rating does not discriminate between vehicle types, so it cannot explain the 37.71% failure rate. Reporting that clearly is more valuable than manufacturing a difference out of the second decimal place.

7. Can we compute the whole KPI header in one query?

$facet runs several independent sub-pipelines against the same input stream and returns all their results in a single document. One pass over the collection, one network round trip, one object for the dashboard to consume.

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" }
      }},
      { $project: {
          _id: 0, total_bookings: 1, completed: 1,
          completion_rate_pct: {
            $round: [{ $multiply: [
              { $divide: ["$completed", "$total_bookings"] }, 100] }, 2]
          },
          completed_value: { $round: ["$completed_value", 0] },
          avg_distance_km: { $round: ["$avg_distance_km", 2] }
      }}
    ],

    by_status: [
      { $group: { _id: "$status", bookings: { $sum: 1 } } },
      { $sort: { bookings: -1 } }
    ],

    payment_mix: [
      { $match: { payment_method: { $exists: true } } },
      { $group: { _id: "$payment_method", bookings: { $sum: 1 } } },
      { $sort: { bookings: -1 } }
    ],

    fare_bands: [
      { $match: { booking_value: { $exists: true } } },
      { $bucket: {
          groupBy: "$booking_value",
          boundaries: [0, 100, 250, 500, 1000],
          default: "1000+",
          output: {
            rides: { $sum: 1 },
            revenue: { $sum: "$booking_value" }
          }
      }}
    ],

    top_routes: [
      { $match: { status: "Completed" } },
      { $group: {
          _id: { pickup: "$route.pickup", drop: "$route.drop" },
          rides: { $sum: 1 },
          revenue: { $sum: "$booking_value" }
      }},
      { $sort: { rides: -1 } },
      { $limit: 10 }
    ]

  }}
])

The headline facet returns 15,000 bookings, 9,343 completed, a 62.29% completion rate, ₹22,02,449 of completed value and 10.87 km average distance. by_status returns the five statuses — Completed 9,343, Cancelled by Driver 2,698, No Driver Found 1,036, Cancelled by Customer 1,022, Incomplete 901. payment_mix returns UPI 45.03%, Cash 25.31%, Uber Wallet 11.79%, Credit Card 10.79%, Debit Card 7.08%.

Bonus: Enriching With $lookup

Our rides documents hold a customer_id but no customer detail. If a customers collection existed, $lookup would join it — always as a left outer join, always returning an array.

db.rides.aggregate([
  { $match: { status: "Completed", booking_value: { $gt: 1000 } } },
  { $lookup: {
      from: "customers",
      localField: "customer_id",
      foreignField: "customer_id",
      as: "customer"
  }},
  { $unwind: { path: "$customer", preserveNullAndEmptyArrays: true } },
  { $project: {
      _id: 0, booking_id: 1, booking_value: 1,
      customer_name: "$customer.name",
      city: "$customer.city"
  }}
])

$unwind converts the one-element array into a plain sub-document. preserveNullAndEmptyArrays: true keeps rides whose customer record is missing — without it, $unwind silently drops them, which is the single most common way an aggregation quietly loses rows.

Section 8: Optimising and Materialising Pipelines

The Golden Rule: $match and $limit as Early as Possible

Every stage pays for the documents it receives. Filtering at the top of the pipeline means every later stage processes a smaller stream, and the saving compounds down the chain.

// SLOW — groups all 15,000 documents, then throws most of the result away
db.rides.aggregate([
  { $group: { _id: "$vehicle_type", revenue: { $sum: "$booking_value" } } },
  { $match: { _id: "Go Sedan" } }
])

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

The same logic applies to $sort: a $sort at the head of a pipeline can be served by an index, while a $sort after a $group must sort in memory — acceptable on seven vehicle types, dangerous on a million customer IDs. And $sort immediately followed by $limit is specially optimised: the engine keeps only the top n in memory rather than sorting the whole stream.

MongoDB's query optimiser will move some $match stages earlier on its own, but it can only do so when it can prove the move is safe. Write the pipeline in the right order yourself and the question never arises. Verify with:

db.rides.aggregate([ … ]).explain("executionStats")
// Check the first stage: IXSCAN is the goal, COLLSCAN is the warning.

A Checklist for Any Pipeline You Write

Materialising Results: $out and $merge

Both stages must be last in the pipeline, and both write the output to a collection instead of returning it to the client.

// Nightly rebuild of a small summary collection
db.rides.aggregate([
  { $match: { status: "Completed" } },
  { $group: {
      _id: { month: { $substr: ["$date", 0, 7] }, vehicle: "$vehicle_type" },
      revenue: { $sum: "$booking_value" },
      rides:   { $sum: 1 }
  }},
  { $out: "monthly_revenue" }
])

// Incremental: recompute one month and merge it into the same collection
db.rides.aggregate([
  { $match: { status: "Completed", date: { $gte: "2024-12-01" } } },
  { $group: {
      _id: { month: { $substr: ["$date", 0, 7] }, vehicle: "$vehicle_type" },
      revenue: { $sum: "$booking_value" },
      rides:   { $sum: 1 }
  }},
  { $merge: {
      into: "monthly_revenue",
      on: "_id",
      whenMatched: "replace",
      whenNotMatched: "insert"
  }}
])

Lab Task — Before You Leave

Key Takeaways