Chapter 07 · advanced · 7 min
Indexes
Everything so far has been about what a table is and how it's shaped. This chapter is about how fast the database can actually find something in it — and EXPLAIN, which shows you the plan Postgres picked, rather than making you take it on faith.
The dataset
One table, sales, 100,000 rows. customer_email has an index; order_code doesn't, even though both columns are equally unique per row — the only difference between the two queries below is whether an index exists.
Schema
| id | SERIAL |
| customer_email | TEXT |
| order_code | TEXT |
| amount | NUMERIC(10,2) |
Without an index, finding one row means checking every row
order_code has no index. EXPLAIN (without running the query — just asking Postgres what it would do) shows how it plans to find one order out of 100,000.
Before you run it — order_code isn't indexed. Do you expect Postgres to check all 100,000 rows, or find some way to skip most of them?
An index gives the planner a shortcut
customer_email has an index (CREATE INDEX sales_email_idx ON sales (customer_email), in this chapter's setup). Same table, same row count, an equally selective filter — the only difference is the index.
Before you run it — compare this plan's first line to the previous one's. Do you expect the same plan shape twice, or something different now that an index exists?
Seeing which indexes actually exist
pg_indexes is a real system view — you can query it like any table to see exactly what's been built, and the DDL that built it.
Before you run it — this chapter's setup created one explicit index. Postgres also auto-creates one for every PRIMARY KEY. How many rows do you expect?
An index isn't free
An index is a second, separate data structure the database keeps in sync with the table. Every INSERT, UPDATE, or DELETE that touches an indexed column has to update the index too, not just the table — so indexing a column you rarely filter on is pure write-cost with no read-side benefit. This is why you don't index every column "just in case": each one is a standing tax on every future write, paid whether or not it ever earns that cost back.
An index only helps when the filter is actually selective
The planner isn't obligated to use an index just because one exists — it estimates the cost of each option and picks the cheaper one. A filter that matches most of the table (e.g. a status column that's 'active' 90% of the time) usually isn't worth the extra step of consulting an index first; reading the table straight through can be cheaper than jumping in and out of it row by row. This is a genuine cost judgment the planner makes per query, not a fixed rule — which is exactly why reading the actual plan with EXPLAIN, rather than assuming, is the skill this chapter is really teaching.
If you've used Excel or Google Sheets
An index is the database equivalent of a phone book being sorted alphabetically instead of by the order people signed up: looking up "Devi" doesn't mean reading every entry, because the sort itself tells you where to stop looking. Excel's MATCH/XLOOKUP scan a range the same unsorted way a Seq Scan does, unless you explicitly sort the lookup column and use a binary-search mode — most spreadsheet users never do, which is part of why a huge VLOOKUP gets slow long before an equivalent indexed database query would.
You've now read a real query plan and watched it change with the schema. That's the same skill Data Lab exercises at a much larger scale — a 300k-row warehouse instead of one seeded table — once you're ready to take it further.