What an Index Actually Is

Every table in PostgreSQL is stored as a heap: an unordered pile of 8 KB pages, each holding a handful of rows. Rows land wherever there is free space. Nothing about the heap is sorted by your primary key, your email column, or anything else.

The sequential scan

Without an index, the only way to answer

select * from users where email = 'ada@example.com';

is to read every page of the table and check every row. That is a sequential scan. On a table with ten thousand rows nobody notices. On a table with fifty million rows, it is the difference between a millisecond and several seconds.

What an index adds

An index is a separate, sorted data structure that maps column values to the physical location of rows in the heap. That location is called a TID (tuple identifier): a page number plus a slot inside the page.

The default index type, and the one you will use most, is the B-tree:

create index users_email_idx on users (email);

A B-tree keeps keys sorted in a shallow, wide tree. Finding one key means walking from the root to a leaf, which usually takes 3 or 4 page reads even for millions of rows. The leaf gives Postgres the TID, and it fetches exactly the heap page it needs.

It is not free

Every index is extra data that must be kept in sync:

• Each insert writes the row to the heap and a new entry into every index on the table.
• Updates that change an indexed column write new index entries too.
• Indexes take disk space and compete for memory with your actual data.

So the rule is not "index everything". It is: index the columns your important queries filter, join, or sort on, and confirm the planner uses them. That is what the next phase is about.

Why Postgres sometimes ignores your index

If a query will return a large share of the table (say, where is_active = true and 95% of users are active), reading the index and then jumping around the heap is slower than just reading the heap in order. The planner estimates this and will happily choose a sequential scan. That is not a bug. It is the planner doing its job.