Lesson 09 — Indexes and query plans

Stack: PostgreSQL · Project: TicketFlow Status: Published Prerequisite: Lesson 08 — Data modeling

Objectives

1. Read EXPLAIN ANALYZE and know what to look for (seq scan, index scan, cost, estimated vs actual rows).
2. Understand what a B-tree index is, which queries use it and which operations kill it.
3. Diagnose and kill the N+1 in Django (select_related/prefetch_related).
4. Design the indexes TicketFlow really needs (and delete the ones it doesn't).

1. The B-tree index in one picture

A B-tree is an ordered tree: searching expires_at = X descends the tree (log n) instead of reading the whole table (n). It serves:

• Equality (WHERE email =...)
• Ranges and ordering (WHERE starts_at >..., ORDER BY created_at)
• Prefixes of composite columns and LIKE 'abc%' (not '%abc')

The golden rule of composite indexes (order left→right): an index (status, expires_at) serves filters by status, by status + expires_at, and ordering by expires_at within a fixed status. It does not serve a search by expires_at alone. Equality column first; range column after.

Costs: every index is paid on every INSERT/UPDATE (writing means maintaining all the trees) and on disk. That is why you don't index "just in case".

2. EXPLAIN ANALYZE: the reading that matters

EXPLAIN ANALYZE
SELECT s.* FROM seat s
WHERE s.event_id = 42
  AND NOT EXISTS (
    SELECT 1 FROM reservation_item ri
    JOIN reservation r ON r.id = ri.reservation_id
    WHERE ri.seat_id = s.id AND r.status IN ('PENDING_PAYMENT','CONFIRMED')
  )
ORDER BY s.sector, s.row, s.number;

What to look at, in order:

1. Seq Scan vs Index Scan: on small tables the seq scan is correct (reading everything is cheaper than the tree); on big ones with a selective filter, it must be an Index Scan. A Seq Scan with a filter returning 3 out of 2M rows = missing index.
2. rows=estimated vs actual: if the estimate says 1 and reality is 50,000, the planner chose badly (stale statistics → ANALYZE table).
3. Buffers (with EXPLAIN (ANALYZE, BUFFERS)): how many pages were read — the real I/O cost.
4. Sort: Sort Method: external merge = sorting on disk = an index that would sort for you is missing.

3. The N+1 in Django

# WRONG: 1 query for reservations + N for users + N for events
for r in Reservation.objects.all():
    print(r.user.email, r.event.title)

# RIGHT: 3 queries total (join for FKs, prefetch for M2M/reverse)
reservas = (Reservation.objects
            .select_related("user", "event")     # FK: JOIN
            .all())
items = (ReservationItem.objects
         .select_related("reservation", "seat")
         .prefetch_related("seat__reservation_items"))  # reverse: 2 queries + stitch in Python

select_related = SQL JOIN (forward FKs). prefetch_related = a second query + stitching in Python (M2M and reverse relations). The universal detector: len(connection.queries) before/after, or django-debug-toolbar.

4. TicketFlow's indexes (the ones it has, the ones it lacks)

Indexes you would NOT breed: low-cardinality columns alone (status alone), small tables, columns that never appear in WHERE/ORDER. Every index is a query hypothesis; no query, no index.

Self-assessment

1. Why does an index (status, expires_at) NOT serve WHERE expires_at < now() alone? Which index would you create for that case?
2. The planner estimates 1 row and reality is 50,000. What do you look at, and which command fixes the most common cause?
3. When is a Seq Scan CORRECT? Give a concrete TicketFlow case.
4. select_related vs prefetch_related: why each in its place? What happens if you use select_related on a reverse relation?
5. What do you pay for every extra index, and how do you decide an index "isn't worth it"?

Continue with the exercises. The solutions only after trying it yourself.