What is an index?
An index is a separate, sorted structure the database keeps so it can find rows without scanning the whole table. A query plan is the database's chosen strategy for answering a query. Reading plans and adding the right indexes is how slow queries become fast.
Why it matters
The single most common production performance problem is a missing index causing
a full table scan. As data grows, a query that was instant in development crawls
in production. Knowing how to read EXPLAIN and add the right index is a skill
that pays off on day one of any real workload.
What to learn
- B-tree indexes and how they speed up lookups and ranges
- Reading
EXPLAINandEXPLAIN ANALYZE - Sequential scan vs index scan vs index-only scan
- Composite indexes and column order
- Indexing foreign keys and columns in WHERE and JOIN
- The write cost of every index you add
- Partial and expression indexes
Common pitfall
Adding an index for every column "just in case." Every index slows down inserts
and updates and uses disk. Index the columns you actually filter, join, or sort
on, confirm the win with EXPLAIN ANALYZE, and remove indexes that no query
uses.
Resources
Primary (free):
- PostgreSQL — Indexes · docs
- PostgreSQL — Using EXPLAIN · docs
- Use The Index, Luke · docs
Practice
Seed a table with a few hundred thousand rows. Run a filtered query and capture
EXPLAIN ANALYZE — note the sequential scan and the time. Add an index on the
filter column, run it again, and compare the plan and timing. Done when you can
explain why the plan changed.
Outcomes
- Read an
EXPLAIN ANALYZEplan and spot a sequential scan. - Add an index that turns a scan into an index lookup.
- Order columns in a composite index correctly.
- Weigh an index's read benefit against its write cost.