Two queries that look alike: one answers instantly, the other hangs and takes the environment down with it. The difference is almost always one word: index. Understanding indexes "on your fingers" pays off for anyone who writes queries: it explains both the application's slow search and why your own innocent SELECT suddenly became a problem.

The full scan: how a database searches without an index

Executing SELECT * FROM orders WHERE customer_id = 42, the database by default does exactly what you'd do with a paper book that has no table of contents: reads everything in a row and sets aside what matches. This is called a full scan (sequential scan). On a thousand rows it's invisible; on a hundred million it's minutes of disk work.

An important consequence: on a small test environment everything flies always. Slow queries are a disease of large volumes — which is why performance problems are invisible on a hundred-record test stand and suddenly bloom in production.

An index is the table's table of contents

An index is an extra structure alongside the table: a sorted list of a column's values with pointers to rows. The exact analogy is a book's subject index: don't leaf through every page — find the word in the alphabetical list and jump straight to it.

With an index on customer_id, the query WHERE customer_id = 42 finds its rows in a fraction of a second regardless of table size. Without one — full scan.

Why not index everything? An index has a price: it takes space and slows down writes — every INSERT and UPDATE must update not just the table but all its indexes. So indexes are created deliberately: the primary key always has one; foreign keys and frequently searched fields get them by the developers' decision.

When the index exists but doesn't work

A treacherous class of situations — the index is there, yet the query still scans the whole table:

  • A function over the column. WHERE lower(email) = 'anna@...' — the index is built on email, but the comparison is on lower(email); to the database it's a different expression. Arithmetic has the same effect: WHERE amount * 100 > 5000.
  • LIKE with a leading percent. LIKE 'anna%' uses the index (like searching by first letters in an alphabetical list), but LIKE '%@gmail.com' doesn't: a table of contents can't search by word endings.
  • Weak selectivity. WHERE status = 'paid' when 90% of orders are paid: it's cheaper to read the whole table than to jump around pointers to nearly every row. The database chooses the scan itself — and that's the right call.

No need to guess — you can ask. Put the word EXPLAIN before the query:

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Instead of data, the database shows its plan: how it intends to execute the query. Reading plans deeply is an art of its own, but the first level is available immediately: seeing Seq Scan (sequential scan) on a big table — there's your slowness; Index Scan — the index is working. That's enough for the conversation with a developer to start not with "something's slow" but with "order search by phone number runs a full scan on a 40-million-row table."

Where this applies

This knowledge works both ways. Inward — about your own queries: a heavy unbounded SELECT on a huge table of a shared environment bothers everyone; add LIMIT and conditions on indexed fields. Outward — about the product: "search responds in 40 seconds" is a performance defect, and after EXPLAIN you'll describe it at the level of cause, not symptom. And a developer's phrase "there's no index there" stops being an incantation — you know what it means and what it threatens.

Where beginners stumble:

  • They measure speed on an empty environment. A hundred rows fly with no indexes at all; performance conclusions from a small database don't transfer to a big one.
  • They believe an index speeds up everything. An index helps specific search conditions — and charges for it with slower writes. "Add more indexes" is not a universal recipe.
  • They write conditions that kill the index — functions over the column, LIKE with a leading percent — and are surprised by the scan.

What to learn next. The "SQL from scratch" series is complete. Its practical application in a tester's work — in SQL for testers. And when you want depth — the PostgreSQL section: transactions and isolation, index internals, EXPLAIN in earnest.