LESSON 6.4
Indexes in practice
Reading a query plan, partial and expression indexes, indexing foreign keys, building indexes on a live table, and knowing when an index is no longer earning its keep.
6 min read
The theory of indexes fits on a page. Using them well on a real system is mostly about three habits: read the query plan, index for the queries you actually run, and treat index changes as production changes.
Read the query plan
Every database will tell you how it intends to run a query. In Postgres that's EXPLAIN (the plan) and EXPLAIN ANALYZE (the plan plus what really happened). The things to look for:
- Scan type. A sequential scan over a large table for a selective filter usually means a missing or unusable index. An index scan or index-only scan is what you hoped for.
- Estimated versus actual rows. If the planner expected 10 rows and got 400,000, its statistics are stale or the data is skewed, and it may pick the wrong plan.
- Sorts and joins. A big in-memory or on-disk sort can often be removed by an index whose order matches the
ORDER BY.
Common reasons an index exists but isn't used: the column is wrapped in a function (WHERE lower(email) = ...), the comparison mixes types, the pattern starts with a wildcard (LIKE '%son'), or the filter matches so many rows that a scan really is cheaper.
Partial and expression indexes
Two features cover a surprising number of real cases.
- Partial indexes index only rows matching a condition. A job queue that queries
status = 'pending'only needs those rows indexed. The index stays tiny even when the table holds years of finished jobs. - Expression indexes index the result of an expression. If logins look up
lower(email), indexlower(email)and the function no longer blocks the lookup.
- Full index on (status, created_at)
- about 3 GB, touched by every write
- Partial index on created_at where pending
- about 30 MB, touched only by pending rows
Foreign keys and joins
Most databases index a primary key automatically but not the column that references it. Deleting a customer then scans the whole orders table to check for references, and joins from customer to orders scan too. Index foreign key columns that you join on or delete through.
Changing indexes on a live system
Adding an index is a production change:
- A plain build can block writes until it finishes, which on a large table means minutes. Use the online form (
CREATE INDEX CONCURRENTLYin Postgres, online DDL in MySQL). - A concurrent build can fail halfway and leave an invalid index behind. Check, drop and retry.
- Dropping an index can be just as risky: some query you forgot depended on it. Many teams first mark it invisible or watch usage statistics for a few weeks.
A short checklist
Add an index
- Turns scans into lookups
- Can remove sorts
- Slower writes
- More memory and disk
Pick it when: A frequent, selective query is slow and the plan shows a scan.
Rewrite the query
- No write cost
- Not always possible
Pick it when: An index exists but the query hides the column behind a function or type cast.
Cache or precompute
- Removes the query from the hot path
- Staleness, invalidation
Pick it when: The query is inherently expensive, like an aggregate over many rows.
Try it
URL Shortener HLD
Decide which columns of the links table need indexes, and which don't.