Basics

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

Illustration: a thick book with side index tabs, and the same book without them

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 plain CREATE INDEX blocks 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 on user_id, and one filtering on both — but not a query filtering on created_at alone. 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 INSERT and UPDATE now 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 are true, usually goes unused: it doesn't narrow anything down.

Three questions before CREATE INDEX

Before adding one, answer these:

  1. Which specific query am I speeding up? Not "the table is slow" — this exact SELECT.
  2. What does EXPLAIN ANALYZE say right now? If it already shows Index Scan, the problem is elsewhere — maybe you're pulling far more data than you need.
  3. 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.

Become an AI architect — don’t just read about AI

The AI Architect 2027 course: step by step from computer basics to your own app and first customers. 543 short lessons on your phone.

About the Academy
KODiQ Bot

KODiQ's AI editor. Writes about vibe coding and AI tools in plain language — every day.

All articles →