How PostgreSQL Indexes Actually Work

Every "why is this query slow" investigation ends in the same place: a query is reading far more rows than it returns, because it has no index to jump straight to the ones it needs. Indexes are the single biggest lever on database performance, and using them well starts with understanding what they actually are, which is less mysterious than it sounds.

This is part 9 of the PostgreSQL Masterclass and the start of the indexing module. We've covered how to query data; now we make those queries fast.

The problem: a table is an unordered pile

A PostgreSQL table is a heap, meaning rows are stored in no particular order, roughly in the order they were inserted. When you ask for WHERE email = 'a@b.com' and there's no index, Postgres has no choice but to read every row and check each one. That's a sequential scan, and its cost grows linearly with the table. At 1,000 rows it's instant. At 50 million it's a timeout.

An index is a separate, ordered data structure that lets Postgres find matching rows without scanning the whole table. The trade is space and write cost: the index takes disk, and every insert, update, or delete has to keep it current. For reads, that trade is almost always worth it.

The B-tree: the index you'll use 95% of the time

The default index type is a B-tree, and it's the right choice for the vast majority of cases. A B-tree keeps keys sorted in a shallow, balanced tree. Because it's sorted and balanced, Postgres can find any key in a handful of steps regardless of table size. A B-tree on 100 million rows is only a few levels deep, so a lookup touches a few pages instead of millions of rows.

CREATE INDEX users_email_idx ON users (email);

That sorted structure is why a B-tree helps with more than exact matches. It efficiently serves:

• Equality: WHERE email = 'a@b.com'
• Ranges: WHERE created_at > '2024-01-01'
• Sorting: ORDER BY created_at can read straight from the index in order
• Prefix matches: WHERE name LIKE 'Ann%' (a leading wildcard like %ann cannot use it)

Whenever you filter, join, or sort on a column, a B-tree on that column is the first thing to reach for.

What "using the index" really costs

There's a detail that explains a lot of confusing behavior. An index entry stores the key and a pointer to the row's location in the heap. So an index lookup is often two steps: find the entry in the index, then fetch the actual row from the table. That second fetch is a random read into the heap, and random reads are slower than sequential ones.

This is why Postgres sometimes ignores an index you expected it to use. If a query will match a large fraction of the table, say 40% of the rows, doing hundreds of thousands of random heap fetches is slower than just reading the whole table sequentially. The planner knows this and picks the sequential scan on purpose. An index helps most when a query is selective, meaning it matches a small slice of the table.

Unique indexes and the automatic ones

Some indexes you get for free. A PRIMARY KEY creates a unique B-tree automatically. A UNIQUE constraint does too. Those are the indexes backing the constraints we discussed earlier, and they're why primary-key lookups are always fast.

What you don't get for free is an index on foreign key columns. As covered in the constraints article, Postgres indexes the primary key but not the referencing side of a foreign key, so those you add yourself.

The write-side cost is real

Indexes are not free money. Every index on a table has to be updated on every write to that table. A table with 8 indexes pays 8 index updates on each insert. On a write-heavy table, over-indexing quietly slows down the exact operations that matter most.

So the discipline is to index for the queries you actually run, not for every column just in case. An unused index is pure overhead: it costs disk, slows writes, and never speeds up a read. Later in the module we'll look at how to find and drop indexes that aren't earning their place.

How to think about it

The practical model to carry:

• No index means a sequential scan, whose cost grows with the whole table.
• A B-tree turns lookups, ranges, and sorts into a few-page operation regardless of size.
• Indexes help most on selective queries; on a query matching much of the table, a scan can win.
• Every index taxes writes, so index deliberately for real query patterns.

That's the foundation. A B-tree on the columns you filter and sort by will fix the large majority of slow queries you'll ever meet. The rest of this module is about the cases the default B-tree doesn't cover, and about proving which index a query actually uses instead of guessing.

Next, we look at the other index types Postgres offers (GIN, GiST, BRIN, and Hash) and the specific problems each one solves that a B-tree can't.