Indexes and EXPLAIN

With a hundred rows in a table, the database looks through them instantly. With a million, reading every row for every query is slow. An index solves that.

An index is like the index at the back of a book

To find the word “transaction” in a book you do not turn every page — you look it up in the alphabetical index at the back and jump to the right page. A database index works the same way: the column’s values are kept separately in sorted order, each pointing to its row.

CREATE INDEX idx_students_city ON students (city);

Now WHERE city = 'Samarqand' reads only the index instead of the whole table.

How the database runs a query: EXPLAIN

EXPLAIN does not run the query; it shows its plan:

EXPLAIN SELECT * FROM students WHERE id = 10;

In the PostgreSQL plan, Index Scan using students_pkey means the primary-key index was used. Seq Scan means the whole table was read row by row.

A search on a column without an index:

EXPLAIN SELECT * FROM students WHERE city = 'Samarqand';

Here the database reads the whole table — city has no index.

When you need an index

• columns used often in WHERE, JOIN … ON and ORDER BY;
• foreign-key columns (grades.student_id) — joins get faster;
• columns that must not repeat — CREATE UNIQUE INDEX.

Primary keys and UNIQUE columns get an index automatically.

PostgreSQL does not create an index on a foreign-key column by itself — add one by hand: CREATE INDEX ON grades (student_id);

The price of an index

An index is not free:

• it takes disk space;
• every INSERT, UPDATE and DELETE must update the index too — writes get slower.

So “an index on every column” is a bad idea. Add an index for a specific slow query, and check the result with EXPLAIN.

When an index does not help

• the column is wrapped in a function: WHERE UPPER(city) = 'TOSHKENT' — a plain index is not used;
• LIKE '%bek' — a pattern with an unknown start;
• a very small table — the database prefers to read it all anyway.

Multi-column indexes

CREATE INDEX idx_orders_customer_date ON orders (customer_id, ordered_at);

It works for WHERE customer_id = 5 and for WHERE customer_id = 5 AND ordered_at > …, but not for WHERE ordered_at > … on its own — order matters (a phone book is sorted by surname first, then first name).

Remember

• An index is a sorted pointer that speeds up searching.
• EXPLAIN shows the query plan; use it to spot full table scans.
• Indexes speed up reads and slow down writes — add them with thought.