The Professor
Index Course index

Module 2 · Databases

Lesson 09 — Indexes and query plans

EXPLAIN, slow queries and the N+1 problem: the lesson that solves TicketFlow availability.

Published
In this lesson
  1. Exercise 1 — Without index vs with index
  2. Exercise 2 — Reading the plan like a senior
  3. Exercise 3 — The N+1, hunted and killed
  4. Exercise 4 — Design the right index
  5. Exercise 5 — The index that gets in the way
  6. Submit

On your PostgreSQL (psql recommended: \timing on). Do not look at solutions.md before submitting.

Setup: generate volume — 20,000 seats, 5,000 reservations and items (a script or generate_series with INSERT...SELECT). Without volume there are no plans to read.

Exercise 1 — Without index vs with index

  1. EXPLAIN ANALYZE SELECT * FROM events_payment WHERE reservation_id = 1234; (or your payments table). Seq Scan? Cost? Estimated vs actual rows?
  2. Create the index CREATE INDEX ON events_payment (reservation_id); and repeat. Note the improvement.
  3. Now filter by something non-indexed with low selectivity (WHERE status = 'SUCCEEDED'): seq scan or index? Why might the planner prefer the seq scan even with an index?

Exercise 2 — Reading the plan like a senior

  1. Run the availability query (07) with EXPLAIN (ANALYZE, BUFFERS).
  2. Identify: scan type per table, whether there is a Sort and with which method, estimated vs actual rows per node.
  3. Change the NOT EXISTS to LEFT JOIN + IS NULL and compare plans. Which one did the planner handle better?

Exercise 3 — The N+1, hunted and killed

  1. In the Django shell: from django.db import connection, reset_queries and count the queries of a loop printing r.user.email for 50 reservations. Note the number.
  2. Same loop with select_related("user"). How many queries?
  3. Add r.event.title to the loop without touching the queryset: how many now? Fix it.
  4. Bonus: reverse relation (r.items.all() per reservation) with and without prefetch_related.

Exercise 4 — Design the right index

For each query, propose the index (columns and order) and justify:

  1. WHERE event_id = X AND state = 'PUBLISHED' ORDER BY starts_at (event)
  2. WHERE gift_card_id = X ORDER BY created_at (lesson 08's ledger)
  3. WHERE user_id = X AND created_at > now() - interval '30 days' (reservation)
  4. WHERE code =? (coupon, looked up on every checkout — critical)
  5. WHERE status = 'PENDING_PAYMENT' AND expires_at < now() (expiration job — the hottest one)

Exercise 5 — The index that gets in the way

  1. Create an obviously useless index: CREATE INDEX ON events_event (description); (long text, never filtered).
  2. Measure INSERTs: before/after with \timing and an INSERT...SELECT of 1,000 rows. How much does each insert pay to maintain the index?
  3. Drop it (DROP INDEX) and write down the rule you will apply before creating any index from now on.

Submit

Paste the trimmed plans and numbers. When corrected we close Lesson 09; next up Lesson 10 — Transactions.

^ top