What is a database index — and why your query reads the whole table

A familiar story: your app was instant. You changed nothing in the code. Then users arrived, the table went from two hundred rows to fifty thousand, and the page takes five seconds.
It's not the code and it's not a weak server. It's that your database dutifully reads the entire table on every request — row by row, from the very beginning — because you never told it where to look. The tool that fixes this is an index. And it's probably the fastest optimisation that exists: one line of code instead of rewriting half the project.
What an index actually does
Picture an 800-page book with no index at the back. You need the word "idempotency" — you'll be flipping through everything. That's exactly how a database runs SELECT * FROM users WHERE email = '[email protected]' when email has no index: it checks every row.
An index is that back-of-the-book listing: a separate structure beside the table where the values are already sorted, each one pointing at its row. The database doesn't scan it — it searches the way you look up a word in a dictionary: open the middle, decide which half, repeat. On a million rows that's the difference between "twenty steps" and "a million steps."
Note: an index doesn't store your data instead of the table — it lives in addition to it. Hence the cost we'll get to below.
How to see that an index is missing
No need to guess; the database will tell you. In Postgres, put EXPLAIN ANALYZE in front of your query:
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;
Look at the first words of the plan:
Seq Scan(sequential scan) — "I'm reading the whole table in order." There's your symptom.Index Scan— "I went through the index." That's what you want.
Next to it you'll see actual time — the real milliseconds spent. That's your "before" measurement. Take it now, before any changes: without a "before" number you can't tell whether you helped.
Which columns deserve an index
The rule is simple: index what you search by, not what you display. Candidates are columns that appear:
- in
WHERE(user_id,email,status); - in
JOIN— that is, the foreign keys your tables connect on; - in
ORDER BY, when you sort large result sets.
Creating one is a single line:
CREATE INDEX idx_orders_user_id ON orders (user_id);
Three details that will save you an evening:
- On a live database use
CREATE INDEX CONCURRENTLY— a plainCREATE INDEXblocks writes to the table for the whole build. On a big table that reads as "the app froze." - An index is a schema change, so it should ship as a migration, not by hand in a dashboard. Otherwise production won't have it.
- Column order in a composite index matters. An index on
(user_id, created_at)helps a query filtering onuser_id, and one filtering on both — but not a query filtering oncreated_atalone. The leftmost part is what works, like a phone book: you'll find someone by surname, not by first name.
What it costs you
Indexes aren't free, which is why "slap one on every column" is a bad strategy:
- Writes get slower. Every
INSERTandUPDATEnow also updates every index on the table. Ten indexes means ten extra updates per insert. - They take space. An index can weigh as much as the data itself.
- On small tables they're useless. With 300 rows the database reads the whole thing faster than it could consult an index — and will deliberately ignore it.
- On near-identical values they barely help. An index on
is_active, where 95% of rows aretrue, usually goes unused: it doesn't narrow anything down.
Three questions before CREATE INDEX
Before adding one, answer these:
- Which specific query am I speeding up? Not "the table is slow" — this exact
SELECT. - What does
EXPLAIN ANALYZEsay right now? If it already showsIndex Scan, the problem is elsewhere — maybe you're pulling far more data than you need. - How write-heavy is this table? If it's a log taking a thousand inserts a second, every extra index is felt.
With those three questions you do in ten minutes what a résumé calls "performance optimisation." And if you're still choosing where to keep your data at all, SQLite versus Postgres covers exactly that.
Does a primary key need an index?
No, it already has one: the database indexes primary keys automatically, along with UNIQUE columns. Foreign keys, however, are not indexed automatically in Postgres — you add those by hand, and their absence is a very common cause of slow JOINs.
Why is my database ignoring my index?
Three usual reasons: the table is too small and scanning is cheaper; the condition is wrapped in a function (WHERE lower(email) = … won't use a plain index on email); or the query returns nearly the whole table anyway — in which case Seq Scan really is faster.
The AI Architect 2027 course: step by step from computer basics to your own app and first customers. 543 short lessons on your phone.





