Can We Find North’s Hot Pumps Without Reading Every Document?

Use a site index to narrow the candidates, then keep the temperature condition. Inspect what an index saves and which work it leaves behind.

14 sections · 4 questions · 1 coding challenge

1 / 14

The arrow keys move between sections too.

Document Queries Still Needed Access Paths

BOTIE

MongoDB’s early documentation included indexes alongside document queries. Storing a flexible object did not remove the older database problem of finding a small result without scanning every stored record. A secondary structure could organise selected field values for repeated reads.

Our teaching index groups documents by site. It exposes candidate reduction without claiming to reproduce MongoDB’s B-tree implementation or planner. We then examine a real compound index as a server-side way to support the site and temperature conditions together.

Sources for this history

The Answer Is Small, but the Search Keeps Reading Everything

The query correctly finds north-site pumps above the threshold. As the register grows, Botie still reads every site’s documents before returning a short answer. Most of that work is spent rejecting records that could never match the requested site.

An index keeps an additional structure arranged for a particular lookup. It can point us toward candidates sooner. We need to understand what it narrows, which conditions still need checking, and how writes keep that structure current.

Candidate counts are exact for this local bucket model. They are not MongoDB execution statistics or timing measurements.

Step 1 of 2

The site bucket is smaller than the register

The request is north with temperature at least 80.

A site-only index has not evaluated the temperature condition.
Asset	Site	Temperature	Role
P1	north	65	Candidate, later rejected
P2	south	99	Outside the selected bucket
P3	north	85	Candidate and match

P1
Site
north
Temperature
65
Role
Candidate, later rejected
P2
Site
south
Temperature
99
Role
Outside the selected bucket
P3
Site
north
Temperature
85
Role
Candidate and match

A site-only index has not evaluated the temperature condition.

Step 2 of 2

Keep the residual check visible

The stored index and final query have different jobs.

1
Index key
site
2
Selected bucket
north documents
3
Remaining predicate
temperature >= 80
4
Projection
matching asset_id values

Dropping the residual filter returns a larger and incorrect result.

An access path supplies candidates, not permission to skip conditions

The site bucket narrows the search while the residual predicate preserves the requested answer.

Narrow by site, then check temperature

Build site buckets, look up the requested site, apply the remaining threshold, and project asset identities.

BOTIE

The register has readings from several sites.

Choose an example

Current reading documents

asset_id

site

temperature

asset_id

site

temperature

Requested site

Temperature threshold

No candidates

Matching asset IDs after candidate filtering

[
  {
    "asset_id": "P1"
  }
]

Follow the operations

1. Current reading documents

[
  {
    "asset_id": "P1",
    "site": "north",
    "temperature": 85
  },
  {
    "asset_id": "P2",
    "site": "south",
    "temperature": 99
  }
]

2. Collect records under a key

{
  "north": [
    {
      "asset_id": "P1",
      "site": "north",
      "temperature": 85
    }
  ],
  "south": [
    {
      "asset_id": "P2",
      "site": "south",
      "temperature": 99
    }
  ]
}

3. Requested site
```
north
```

4. Look up one exact key

{
  "found": true,
  "value": [
    {
      "asset_id": "P1",
      "site": "north",
      "temperature": 85
    }
  ]
}

5. No candidates
```
[]
```

6. Use a lookup or its fallback

[
  {
    "asset_id": "P1",
    "site": "north",
    "temperature": 85
  }
]

7. Temperature threshold
```
80
```

8. Keep matching document values

[
  {
    "asset_id": "P1",
    "site": "north",
    "temperature": 85
  }
]

9. Keep selected document paths
```
[
  {
    "asset_id": "P1"
  }
]
```
10. Result
```
[
  {
    "asset_id": "P1"
  }
]
```

The browser runs an explicit model of the mechanism. It does not connect to MongoDB or Redis, simulate their query planners, or establish their concurrency guarantees.

1. Distinguish the answer size from the search work

A query returning one document might inspect a million candidates. Count work along the access path as well as returned results. In a real database, distinguish keys examined, documents examined and documents returned.

Our model counts documents in the selected site bucket. Building that bucket still scans the initial input. We do it inside the worksheet to make its contents visible; a real index is maintained across requests rather than rebuilt for every query.

A smaller displayed answer does not by itself demonstrate an efficient lookup.

2. Build an entry that points to the relevant records

For each document, read site and append the document to that site’s bucket. Multiple pumps share a site, so one site key must retain a list. Then a lookup can select just that list.

A database index generally stores keys and references to records in a specialised structure. Copying entire documents into Python lists is an explanatory model. Its memory use and complexity should not be presented as measurements of the server implementation.

The grouping operation from the first NoSQL workshop now has a narrower role: candidate selection.

3. Apply conditions the index has not decided

The north bucket contains both a temperature of 65 and one of 85. Looking up north proves only the site condition. Filter those candidates against the threshold before returning results.

If the requested site is absent, its candidate list is empty. That should produce no matches rather than falling back to all documents. A “helpful” fallback would change the query’s meaning.

Every surviving record still needs to satisfy the complete predicate.

Inspect the bucket before the final filter

A lookup can reduce work while leaving a real decision to make.

Python

buckets = {"north":[{"asset_id":"P1","temperature":65},{"asset_id":"P3","temperature":85}]}
candidates = buckets.get("north", [])
print(len(candidates))
print([row["asset_id"] for row in candidates if row["temperature"] >= 80])

Output

2
['P3']

There are two candidates and one match. The number of candidates alone does not reveal which pump qualifies.

4. Choose compound field order from the request

A MongoDB compound index orders more than one field. An index beginning with site can support its leading site prefix. Adding temperature after site can support a range within a selected site, depending on the query and plan.

A request using only temperature does not generally receive the same efficient leading-prefix lookup from that field order. Sort requirements can also influence the useful order. Compare actual candidate plans with explain instead of treating a mnemonic as a guarantee.

The JSON below is an index key specification passed to create_index or createIndex.

Run in your own environment

Index site before temperature for this access pattern

This compound index key pattern fits equality on site followed by a temperature range. The 1 values specify ascending index order.

Create the index on a disposable MongoDB assets collection outside the browser, then inspect representative find queries with explain("executionStats") in mongosh.

json

{"site":1,"temperature":1}

Check keys and documents examined as well as returned rows. The local bucket model does not claim to reproduce the selected plan.

Index site before temperature for this access pattern. Documentation checked 2026-10-16.

5. Budget for writes and storage too

An index must reflect inserts, deletes and changes to indexed fields. More indexes can improve particular reads while adding storage and write work. A useful test workload therefore includes the writes that occur while the maintenance screen is open.

A unique index also enforces a particular uniqueness rule, subject to the product’s supported configuration. An ordinary non-unique index improves an access path without making a field unique. Do not infer an integrity constraint merely because a query is fast.

Keep the reason for each index beside the workload it supports.

6. Know when an array creates several index entries

MongoDB can create multikey indexes for array values. One document can contribute several keys. Counting matching index entries is therefore not always the same as counting distinct matching documents. Compound multikey indexes also have restrictions, including constraints on indexing multiple array fields within one document.

Our site example uses a scalar field deliberately. Before applying its intuition to an array, inspect the actual index rules and the execution plan. Keep the same-member predicate from the previous lesson when array fields are involved.

An index accelerates a meaning you have already specified. It does not repair an incorrect predicate.

7. Measure the case where nearly everything matches

If almost every document belongs to north, a site bucket barely narrows the work. Another index or a scan may be more appropriate for the workload. Query planners make choices based on supported plans and their estimates; production distributions can change.

Test a busy site, an absent site and a changed threshold. Record actual server statistics and latency under representative load. The browser’s small candidate count is evidence about this construction only.

The following lesson uses selected documents as input to a pipeline, where each stage can change the grain again.