Indexes, EXPLAIN and query performance

A query that is instant on your laptop with fifty rows can take seconds on production with five million. The usual reason is a missing index — the database is scanning every row to find the ones you want, because you never told it to keep a sorted lookup structure for that column. This lesson is how indexes work, how to see whether the database is using one (with EXPLAIN), and the discipline of indexing what you query without indexing everything.

What an index is, in one paragraph

An index is a separate, sorted data structure the database maintains for one or more columns — think of the index at the back of a book. Without it, finding every patient in Pune means reading every row and checking the city (a "full table scan", O(n)). With an index on city, the database jumps straight to the Pune entries (roughly O(log n)). The cost: the index takes disk space and must be updated on every insert and update to that column. So an index trades a little write cost and storage for a large read speed-up — worth it for columns you filter or sort on often, wasteful for columns you never query.

Adding an index in Django

Two ways. For a single field, db_index=True:

class Patient(models.Model):
    city = models.CharField(max_length=60, db_index=True)

For anything more — multi-column indexes, named indexes — use Meta.indexes, which is the modern, preferred form:

class Patient(models.Model):
    city = models.CharField(max_length=60)

    class Meta:
        indexes = [
            models.Index(fields=["city"]),                    # single column
            models.Index(fields=["city", "created_at"]),      # composite, for filter+sort together
        ]

Indexes are schema, so they arrive through migrations. Verified: adding Index(fields=["city"]) produced a migration creating patients_pa_city_9ac415_idx, and the index appears in the database schema. Note: Django already indexes primary keys and ForeignKey columns automatically — you do not add indexes for id or for the foreign-key columns; you add them for the other columns you filter and sort on.

Seeing whether an index is used: EXPLAIN

You do not guess whether an index helps — you ask the database, with EXPLAIN. Django exposes it as .explain() on any queryset:

print(Patient.objects.filter(city="Pune").explain())
# verified (SQLite):
# SEARCH patients_patient USING INDEX patients_pa_city_9ac415_idx (city=?)

The word that matters is USING INDEX — the database is using the index to search straight to the Pune rows. Without the index, the same query would say SCAN patients_patient — reading every row. SEARCH ... USING INDEX good, SCAN (on a large table you filter) bad. On PostgreSQL the output differs in wording ("Index Scan" versus "Seq Scan") and includes cost estimates, but the question is the same: is it using my index, or scanning the whole table? EXPLAIN is how you answer it with evidence instead of assumption.

Index what you query — and only that

The discipline is not "index everything" (that slows every write and wastes space) and not "index nothing" (that scans on every read). It is: index the columns you actually filter, order, or join on frequently. A practical checklist for a column:

• Do you filter() or exclude() on it often? → likely index it.
• Do you order_by() it on large result sets? → likely index it (a composite index matching the filter-then-sort pattern helps most).
• Is it a ForeignKey? → already indexed, do nothing.
• Do you never query it directly (a notes field you only display)? → do not index it.

For Nidaan, Patient.city is worth indexing (staff filter by city constantly); a patient's notes field is not (never filtered). The order of columns in a composite index matters too: Index(fields=["city", "created_at"]) helps a query that filters by city and sorts by date, because the index is sorted city-first then date-within-city.

Measure, then index — do not guess

The right workflow is evidence-driven, and it mirrors the N+1 lesson's habit:

1. Find the slow query — the Django Debug Toolbar shows per-query timings in development; production databases have slow-query logs.
2. EXPLAIN it — see whether it scans or searches.
3. Add the index the query needs, migrate, and EXPLAIN again to confirm it now uses the index.
4. Re-measure — confirm the query is actually faster.

Adding indexes blindly "to be safe" is a real anti-pattern: every extra index slows down inserts and updates (each write must maintain every index on that table) and consumes storage, so a table drowning in speculative indexes writes slowly for no read benefit. Index in response to a query you have seen is slow, verified with EXPLAIN — not on a hunch. This is the same principle as the whole ORM-depth module: let the database do the work, and use the database's own tools to see what it is doing.

Check your work

What an index is and its trade-off. A sorted lookup structure for a column that turns a full scan (O(n)) into a fast search (≈O(log n)), at the cost of storage and slower writes to that column.

How to add one. db_index=True for a single field, or Meta.indexes with models.Index(fields=[...]) for single or composite; they arrive via migrations. Verified: the index was created and present in the schema.

What is already indexed. Primary keys and ForeignKey columns automatically — you index the other columns you query.

How to check usage. queryset.explain(); look for USING INDEX/"Index Scan" (good) versus SCAN/"Seq Scan" (a full scan). Verified: the city filter reported SEARCH ... USING INDEX.

What to index. Columns you frequently filter, order, or join on — not everything (slows writes, wastes space) and not nothing (scans on reads); composite index order should match the filter-then-sort pattern.

The workflow. Find the slow query, EXPLAIN it, add the needed index, re-EXPLAIN and re-measure — evidence, not hunches.

Practice

1. Add Index(fields=["city"]) to Patient, migrate, and run Patient.objects.filter(city="Pune").explain(); confirm it reports USING INDEX.
2. Remove the index, migrate, and explain() the same query; confirm it now reports a SCAN (full table scan).
3. Confirm you do not need to add an index for a ForeignKey column — check that a filter on a FK already uses an index in EXPLAIN.
4. Add a composite Index(fields=["city", "created_at"]) and explain() a query that filters by city and orders by created_at; observe the index being used.
5. Install Django Debug Toolbar and find the slowest query on a page; EXPLAIN it and decide whether an index would help.
6. Argue in three sentences why adding an index to every column of a write-heavy table is a mistake.

Official documentation

• Django — Meta.indexes — Declaring single and composite indexes.
• Django — QuerySet.explain() — Running EXPLAIN from the ORM.
• Django — Database optimization — Indexing and the measure-first approach.

Next: dropping to raw SQL, and when it is the right call.