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
  2. Exercise 2
  3. Exercise 3
  4. Exercise 4
  5. Exercise 5
  6. Professor's summary

Exercise 1

  1. Without the index: Seq Scan with a filter, high cost, and estimated ≈ actual rows if statistics are fresh.
  2. With the index: Index Scan (or Bitmap Heap Scan + Index), cost drops an order of magnitude; if the table is cached, shared hit dominates in BUFFERS.
  3. With low selectivity (status nearly uniform), the planner may prefer a Seq Scan: reading the index + jumping to the table for 40% of the rows is more expensive than reading everything once. The index shines with high selectivity (few results).

Exercise 2

  1. Expected: Index Scan on seat by (event_id,...), a subplan with Index Scan on reservation_item by seat_id (the conditional UNIQUE creates an index), and a top-N Sort (quicksort in memory if few rows). Actual vs estimated rows with a reasonable deviation percentage.
  2. The planner usually picks equivalent plans; NOT EXISTS wins on clarity and sometimes on plan with an anti-join. The honest conclusion: measure, don't guess — and write the one that reads better.

Exercise 3

  1. ~51 queries (1 + 50). 2. With select_related: 1 (JOIN). 3. Adding event without fixing: 51 again (now per event); with select_related("user", "event"): 1. 4. Reverse relations: without prefetch = N+1; with prefetch_related("items") = 2 queries. Rule: select_related for forward FKs; prefetch for reverse and M2M.

Exercise 4

  1. (event_id, state, starts_at) — or (state, starts_at) if you always filter state first; since event_id is equality and starts_at is range/order: equalities first, ordering last.
  2. (gift_card_id, created_at) — equality + ordering: the index returns the ledger already sorted, no Sort node.
  3. (user_id, created_at DESC) — equality + recent range.
  4. code UNIQUE — beyond the index, it guarantees the coupon cannot be duplicated (business rule).
  5. (status, expires_at) — already exists from 00b; it is THE hot query: equality + range. If the job only processes PENDING, consider a partial index: CREATE INDEX... WHERE status = 'PENDING_PAYMENT' (even smaller).

Exercise 5

  1. INSERTs slow down (worse on tables with many indexes: every tree gets the insert too). Typical numbers: 10-30% slower with a useless long-text index.
  2. Proposed rule: an index is created with the query that justifies it in hand (or the integrity constraint). No query, no index — and pg_stat_user_indexes audits usage (idx_scan = 0 → candidate for deletion).

Professor's summary

  • B-tree for equality/range/order; composites: equalities first, range/order after; prefixes matter.
  • EXPLAIN ANALYZE: scan type, estimate vs reality, Sort in memory/disk, buffers.
  • The N+1 is killed with select_related (JOIN) and prefetch_related (2 queries); detected by counting queries.
  • Every index is a hypothesis: created with the query in hand, audited, and dropped when nobody uses it.

After the correction: Lesson 10 — Transactions.

^ top