What an Index Is

~12 min · indexes, concepts

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

The book's index — same idea

An index is the database's back-of-the-book index. Looking up "transactions" in the index of a 600-page book takes a glance; reading every page until you find the word takes hours. PostgreSQL indexes work the same way: they're a separate sorted structure that points back to the table rows, turning O(N) scans into O(log N) lookups.

The trade-off

Indexes are not free. Every INSERT, UPDATE, or DELETE has to update every index on the table. Indexes also take disk space — sometimes more than the table itself. The right number of indexes is the smallest set that supports the queries that actually matter. Adding an index "just in case" is exactly how schemas decay.

What gets indexed automatically

PostgreSQL creates an index automatically for: PRIMARY KEY, UNIQUE constraint. Foreign keys do NOT get an index automatically — this is one of the most common performance pitfalls. Always index your foreign keys.

Code

Create, list, drop·sql

CREATE INDEX users_email_idx ON users (email);

-- See indexes on a table
SELECT indexname, indexdef
FROM   pg_indexes
WHERE  tablename = 'users';

DROP INDEX users_email_idx;

Watch the planner pick the index·sql

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE email = 'alice@example.com';
-- Look for: Index Scan / Index Only Scan / Bitmap Heap Scan.
-- A 'Seq Scan' on a large indexed column means the planner skipped the index
-- (often because of low selectivity, type mismatch, or stale statistics).

External links

Exercise

On any table you control, write a query whose WHERE clause filters on a non-indexed column. Run EXPLAIN ANALYZE; note the Seq Scan. Add an index on that column; re-run; observe the plan change to Index Scan and the timing improvement.