Index Design — Order, Covering, Partial, TTL

Goal

On 60,000 orders, you measure the same list query with no index, in the field order of the query (ERS), and as equality→sort→range (ESR) and compare the numbers, and confirm covered queries, partial indexes, TTL, and regex prefixes through the numbers in the execution plan. At the end, you sort out an incident where three screens got slow.

Why it matters

Index problems are usually not "it is missing" but "it does not fit". One change of order makes the keys scanned differ by thousands of times, and one regex makes a plan that says IXSCAN scan every key. If you look only at the stage name you miss it; you see it if you look at the three numbers totalKeysExamined, totalDocsExamined, and nReturned and whether there is a SORT stage. This lab has you measure those numbers yourself, and the grader measures the same numbers again to match them.

Steps

1. Load the material and take a baseline (no index)
2. An index built in the field order (ERS)
3. An equality→sort→range index (ESR) and the planner's choice
4. A query that does not open documents (covered)
5. A partial index
6. TTL and string times
7. Regex prefixes
8. Incident — the queries of three screens got slow

Connecting and material

python3 /opt/lab/fixtures/mg/mgkit.py load-index    # idx.orders 60,000건(다시 돌리면 인덱스까지 처음 상태로)
mongosh idx

The list screen's query (steps 1–3): db.orders.find({status: "shipped", amount: {$gte: 150000}}).sort({createdAt: -1}).limit(20)

Notes

• Put result files under /root/work/mgidx/ (mkdir -p /root/work/mgidx).
• To measure with a specific index, append .hint({<keys>}); to measure without an index, append .hint({$natural: 1}).
• Common mistakes: feeling safe just from the stage name (IXSCAN), and not excluding _id in the projection so the query is no longer covered.
• Incident tool: incident list shows the incidents you can start and a one-line symptom for each, and incident show <name> shows the ticket again. Starting the same incident again resets it to the initial broken state with new random material (answers you wrote earlier will no longer match).
• The report is /root/incident/<name>/report.txt. Write one <target>: <cause-code> line per target, and add evidence: <line> at the end. The evidence is one line of output you saw before fixing, copied verbatim (20 characters or more) — the grader matches it whole against the observation record captured during the incident. The grader looks at the actual state after your fix first, and uses the report to confirm your diagnosis.
• The lab Pod has no volume. When the session ends, your work under /root disappears.

Load the material and measure a baseline

Load 60,000 orders into idx.orders with python3 /opt/lab/fixtures/mg/mgkit.py load-index. Read the list screen's query db.orders.find({status: "shipped", amount: {$gte: 150000}}).sort({createdAt: -1}).limit(20) with explain('executionStats') in the idx DB without an index, and write four lines plan:, keysExamined:, docsExamined:, and nReturned: to /root/work/mgidx/01-baseline.txt. plan is the stage names joined with >, starting from the outer stage (winningPlan) and following inputStage.

Direction: you can compare against the fixed state only if you measure before fixing. This query filters for status equal and amount at or above 150000, then sorts by createdAt and uses only 20 — see what it scans, and how much, when there is no index. Tools: explain('executionStats'), queryPlanner.winningPlan and the inputStage inside it, the three numbers in executionStats. Specifics: the outside of the plan tree is what happens last. If there is a sort stage, go down and see what is inside it.

idx.orders still has 60,000 documents · the four values in 01-baseline.txt match the no-index plan

Build an index in the order the query lists the fields

Create the index {status: 1, amount: 1, createdAt: -1}, measure the same list query with that index (hint), and write the same four lines to /root/work/mgidx/02-ers.txt. See what changed from the baseline and what stayed the same.

Direction: using an index is not the end — check whether a sort stage remains and how many keys were scanned. Tools: createIndex, cursor.hint({<keys>}), explain('executionStats'). Specifics: the keys scanned over a range (amount) are not in createdAt order. So a separate sort gets attached somewhere in the plan — write down where that stage is.

The {status: 1, amount: 1, createdAt: -1} index exists and the four values in 02-ers.txt measured with it match

Build it in equality → sort → range order

Create the index {status: 1, createdAt: -1, amount: 1}, measure the same list query with that index, and write the same four lines to /root/work/mgidx/03-esr.txt. Then add one line winner: <indexName> with the name of the index the planner chose when asked without a hint.

Direction: if the inside of the equality range is already in createdAt order, it can walk in that order, filter amount, and stop at 20. Tools: createIndex, hint, indexName in the winningPlan of an explain() without a hint, and the losing plans in rejectedPlans. Specifics: put keysExamined and plan side by side with step 2. The planner briefly runs the candidate plans and picks the one that produced results first.

The four values in 03-esr.txt measured with the {status: 1, createdAt: -1, amount: 1} index and winner match the current plan

Answer without opening documents

One list screen shows only createdAt and amount of orders whose status is paid and whose createdAt is on or after 2026-10-01T00:00:00Z. Write this screen's query to /root/work/mgidx/04-covered.json as {"filter": …, "projection": …} (dates in Extended JSON {"$date": "…"}), and make it answer without opening a single document (totalDocsExamined: 0). You may add an index if needed.

Direction: if all the fields to return are inside the index keys, there is no reason to open documents. Tools: projection, the ESR index from step 3, totalDocsExamined and PROJECTION_COVERED in explain('executionStats'), and EJSON.stringify to check dates. Specifics: there is one field that comes along by default even if you do not write it in the projection — if that field is not in the index, documents get opened because of it.

The query in 04-covered.json gives the same result as that screen, and totalDocsExamined is 0

An index that holds only the documents you need

For a screen that finds unprocessed orders (status: "pending") per customer, create a partial index on customerId that holds only documents whose status is pending. In /root/work/mgidx/05-partial.txt, write three lines: the number of keys actually in that index partialKeys: <n>, the plan of customer 3728's query {customerId: 3728, status: "pending"} as withStatus: <plan>, and the plan of the query {customerId: 3728} as withoutStatus: <plan> (plan is the stage names joined with >, as in step 1).

Direction: a partial index is small and cheap, but it can be used only when the query condition includes the index's condition. Tools: partialFilterExpression of createIndex, the key count from db.orders.validate().keysPerIndex, and the plans from explain(). Specifics: if you ask without status, the planner sees that the index does not hold every document and so cannot be used — write down how the two plans differ.

The customerId partial index exists, and the key count and the plans of both queries in 05-partial.txt match the current state

Delete old sessions with TTL

Load 300 documents into idx.sessions with python3 /opt/lab/fixtures/mg/mgkit.py load-sessions (running it again resets them). Make the database itself delete sessions once 1 hour (3600 seconds) has passed since the last access (lastSeen). Some sessions written by an old app have lastSeen as a string — those must be deleted too. Do not delete sessions that should stay alive, and carry the times over unchanged.

Direction: a TTL index is handled by a background monitor that looks at the date field every 60 seconds and deletes. Values that are not dates are simply passed over. Tools: createIndex(…, {expireAfterSeconds: …}), $type to count strings, $toDate in an update pipeline. Specifics: after converting the strings to dates, wait about a minute and count what is left again. If you delete by hand, sessions that expire next will remain again.

lastSeen TTL of 3600 seconds · 0 string times · expired sessions deleted by TTL and live sessions kept

A regex narrows the range only as a prefix

Create an index on sku in orders, measure the three queries {sku: /^TN-123/}, {sku: /TN-123/}, and {sku: /^tn-123/i}, and write keysExamined=<n> nReturned=<n> on three lines anchored:, unanchored:, and ignoreCase: in /root/work/mgidx/07-prefix.txt.

Direction: the plan says IXSCAN for all three queries. The difference is not in the stage name but in how many keys were scanned. Tools: createIndex, totalKeysExamined and nReturned in explain('executionStats'), indexBounds. Specifics: look at indexBounds too — only the regex with a fixed start has a narrow range; the rest go from start to end.

The queries use the sku index, and the keys scanned and documents returned for the three queries in 07-prefix.txt match the current state

Incident — the queries of three screens got slow

Running incident mg-slow-endpoints starts the incident (it creates a new inc DB unrelated to the earlier steps). The queries of screens ep1, ep2, and ep3 (/root/slowapp/queries/<ep>.json) all trigger slow-query alerts. Run them with /root/slowapp/slowapp run|explain|indexes <ep>, find the cause for each screen, and fix it so that, leaving the returned results unchanged, it scans only as much as needed, without a full scan (COLLSCAN) or an in-memory sort (SORT). The email screen is a screen that searches case-insensitively. Do not delete or change data. In /root/incident/mg-slow-endpoints/report.txt, write three lines ep1: <code>, ep2: <code>, and ep3: <code> and evidence: <line> (one line of slowapp explain or slowapp indexes output seen before fixing, verbatim). The code for each screen is one of the following: collation-mismatch (the query and the index have different collations, so the index cannot be used), hidden-index (the right index is hidden), index-missing (there is no right index at all), sort-after-range (the index puts the sort field after the range, so it sorts in memory), unanchored-regex (the start of the regex is open, so it scans every key).

Direction: for each screen, look at "the plan (explain)" and "the index list of that collection" side by side. If it does a full scan while a fitting index is in the list, the cause is why the planner cannot use it. If it uses an index but still scans many keys or has a SORT, the cause is the shape of the index or the shape of the query. Tools: slowapp explain and slowapp indexes, mongosh's unhideIndex, createIndex, dropIndex, and collation and $regex in the query files. Specifics: the place to fix differs per screen — tell whether it is on the index side (restoring it, a new index in a different order) or the query side (adding a collation, anchoring the start of the regex). After fixing, check with slowapp explain that keys and docs came close to returned, and that the count from slowapp run is unchanged.

All three screens return the same results and scan only as much as needed without COLLSCAN or an in-memory SORT · cause codes and an evidence line