Database Indexes

What is it? #

An index is a separate structure that lets the database find rows without scanning the whole table.

Without one, finding a row among ten million means reading all ten million. With one, it is a handful of steps through a tree — the binary search idea applied to storage.

Indexes are not free. Every insert, update and delete must also update every index on that table, and each index consumes disk space.

So the job is not "index everything". It is indexing the columns you filter, join and sort by, and nothing else.

Think of it like this #

The index at the back of a book. Without it, finding every mention of a topic means reading every page. With it, you look up the word and jump to the listed pages.

The index took effort to compile and takes up pages of its own. Adding one for every word in the book would double its size and nobody would maintain it.

Simple example #

An orders table with ten million rows. Finding orders for one customer takes seconds without an index and milliseconds with one. Adding a second condition and a sort changes which index helps.

Code #

-- Slow: no index means scanning every row
SELECT * FROM orders WHERE customer_id = 4711;

CREATE INDEX idx_orders_customer ON orders (customer_id);
-- Now the same query jumps straight to the matching rows


-- Composite index: column order matters
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at DESC);

-- This uses the index fully
SELECT * FROM orders
WHERE customer_id = 4711
ORDER BY created_at DESC
LIMIT 20;

-- This uses it too (leftmost prefix)
SELECT * FROM orders WHERE customer_id = 4711;

-- This CANNOT use it: created_at is not the leading column
SELECT * FROM orders WHERE created_at > '2026-09-01';


-- Partial index: only the rows you actually query
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';       -- much smaller than a full index


-- Unique index: correctness as well as speed
CREATE UNIQUE INDEX idx_users_email ON users (lower(email));


-- Find out what the database actually does
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 4711 ORDER BY created_at DESC LIMIT 20;

Reading EXPLAIN output

Seq Scan on orders          reading every row — usually the problem
Index Scan using idx_...    using an index — good
Bitmap Heap Scan            index found many rows, fetching them in batches
rows=1  actual rows=48000   the planner's estimate was wrong: statistics are stale

Things that stop an index being used

WHERE lower(name) = 'ravi'        function on the column — index a function instead
WHERE customer_id::text = '4711'  type mismatch forces a cast
WHERE name LIKE '%shop'           leading wildcard cannot use a normal index
OR across different columns       often needs separate indexes or a rewrite

How it works #

Most indexes are B-trees: a balanced structure where each step narrows the search, giving logarithmic lookup. The database also stores a pointer from the index entry back to the row.

A composite index is ordered by the first column, then the second within it. That is why the leftmost prefix rule exists: an index on (customer_id, created_at) helps queries filtering on customer_id alone, but not queries filtering only on created_at.

Including the sort column in the index, in the right direction, lets the database return rows already ordered. Without it, the query fetches matching rows and sorts them — which is where the time goes on a large result.

A partial index covers only rows matching a condition. If 2% of orders are pending and that is what you query, the index is tiny and fast to maintain.

A unique index enforces correctness and provides speed. Indexing lower(email) makes the uniqueness case-insensitive, which is almost always what you want for email addresses.

EXPLAIN ANALYZE runs the query and reports the actual plan and timings. A Seq Scan on a large table with a selective filter is the clearest sign an index is missing. A large gap between estimated and actual rows means the statistics need updating.

The list of index-defeating patterns is worth memorising; each is a common reason an index that exists is not being used.

Real-world use #

Missing indexes are the most common cause of slow database queries, and adding one is often a hundredfold improvement for a few seconds of work.

The counterweight is write cost. A table with eight indexes writes slowly, and bulk imports can be much faster if indexes are dropped and rebuilt afterwards.

Every foreign key column should generally be indexed. Databases do not always create these automatically, and joins and cascading deletes depend on them.

Production databases expose statistics on index usage. Unused indexes are pure cost and should be dropped; frequently scanned tables without indexes are the queue of work to do.

Creating an index on a large live table can lock it. PostgreSQL offers CREATE INDEX CONCURRENTLY for exactly this reason, and knowing about it prevents a self-inflicted outage.

Common mistakes #

• Indexing every column, making writes slow and wasting space.
• Getting composite index column order wrong, so the index goes unused.
• Applying a function to the indexed column in the WHERE clause.
• Forgetting indexes on foreign key columns.
• Creating an index on a large production table without the concurrent option, locking writes.

Practice #

Create a table with 100,000 rows. Time a filtered query, add an index, and time it again. Then run EXPLAIN ANALYZE before and after and identify where the plan changed. Finally, write a query that cannot use your index and explain why.