PostgreSQL Indexes Explained: B-Tree Basics
On this page
A slow query without an index
Here's an experiment you can run yourself. Create an orders table with half a million rows:
CREATE TABLE orders (
order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id int NOT NULL,
order_date date NOT NULL,
status text NOT NULL,
amount numeric(10,2) NOT NULL
);
INSERT INTO orders (customer_id, order_date, status, amount)
SELECT (random() * 50000)::int,
DATE '2024-01-01' + (random() * 900)::int,
(ARRAY['paid', 'shipped', 'cancelled'])[1 + (random() * 2)::int],
round((random() * 500)::numeric, 2)
FROM generate_series(1, 500000);
ANALYZE orders;Now find one customer's orders, and ask PostgreSQL how it did it:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 4242;Seq Scan on orders (actual time=0.624..30.145 rows=11 loops=1)
Filter: (customer_id = 4242)
Rows Removed by Filter: 499989
Execution Time: 30.220 msSeq Scan means PostgreSQL read every row and threw away 499,989 of them to find 11. On a laptop that's 30 ms. On a busy server with a table 100 times bigger, it's seconds, many times a second.
How a B-tree index works
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 4242;Bitmap Heap Scan on orders (actual time=0.028..0.041 rows=11 loops=1)
-> Bitmap Index Scan on orders_customer_id_idx
Index Cond: (customer_id = 4242)
Execution Time: 0.062 msAbout 500 times faster. Your exact numbers will differ, but the gap will be similar.
An index is a separate structure that keeps a column's values in sorted order, each with a pointer to its row. The default kind is a B-tree, short for "balanced tree", not "binary tree". It works like a book's index: a few levels of signposts lead to a sorted list, and the sorted list points to the pages. Each level fans out to hundreds of children, so even a table with hundreds of millions of rows is only three or four levels deep. Every lookup takes about the same few steps.
Because the bottom level is sorted, a B-tree handles more than equality. It's good for ranges (>, <, BETWEEN), ORDER BY, and prefix searches like LIKE 'abc%' (with the right collation or operator class).
PostgreSQL creates a B-tree for you on every primary key and UNIQUE constraint. Foreign key columns don't get one automatically, and they're usually worth indexing, because you join and filter on them all the time.
What an index costs
Check the sizes:
SELECT pg_size_pretty(pg_relation_size('orders')) AS table_size,
pg_size_pretty(pg_relation_size('orders_customer_id_idx')) AS index_size;
-- 30 MB | 4552 kBThat one index is about 15% of the table. Every INSERT now also writes to it, and so does every UPDATE of customer_id and every DELETE. Five or six indexes on a busy table can take more space than the table and slow every write. So index the columns your real queries filter, join and sort on, not every column "just in case".
Composite indexes and skip scan
A composite index sorts by the first column, then by the second within each first value, like a phone book sorted by last name and then first name:
DROP INDEX orders_customer_id_idx;
CREATE INDEX orders_customer_date_idx ON orders (customer_id, order_date);That serves WHERE customer_id = 4242 on its own, and customer_id = 4242 AND order_date >= '2025-01-01' even better. You don't need a separate index on customer_id as well.
What about filtering on order_date alone? Traditionally the answer was "the index can't help". Imagine a phone book and a request for everyone named Sam: you'd read the whole book. PostgreSQL 18 added skip scan. If the first column has only a few distinct values, PostgreSQL jumps through the index once per value. Here's an index led by status, which has three values:
CREATE INDEX orders_status_date_idx ON orders (status, order_date);
EXPLAIN ANALYZE SELECT order_id FROM orders WHERE order_date = '2025-03-01';
-- Bitmap Index Scan on orders_status_date_idx ... Index Searches: 7The same query against (customer_id, order_date) still does a sequential scan, because 50,000 customers means 50,000 jumps. So the rule still holds for design: put the columns you filter with = first, and range columns after them.
When PostgreSQL ignores your index
Having an index doesn't mean it gets used. The planner estimates the cost of each option from table statistics and picks the cheapest. A sequential scan wins in these common cases:
- The query matches a large share of rows.
WHERE status <> 'cancelled'matches about three quarters of the table. Jumping from index to table row by row would be slower than one straight read. - The table is tiny. If it fits in a few pages, reading them all is cheapest. That's why an index can look useless on your development database and then save you in production.
- The column is wrapped in a function.
WHERE lower(status) = 'paid'can't use an index onstatus, because the index storesstatus, notlower(status). Day 23 shows expression indexes that fix this. - Statistics are stale. After a big bulk load, run ANALYZE so the planner knows what the table looks like now.
Seeing a Seq Scan isn't automatically a bug. It's a prompt to check which of these applies.
Index-only scans and covering indexes
A normal index scan finds the row pointers in the index, then visits the table to fetch the other columns. If the index already contains every column the query needs, PostgreSQL can skip the table:
VACUUM orders;
EXPLAIN ANALYZE SELECT order_date, status FROM orders WHERE order_date = '2025-03-01';
-- Index Only Scan using orders_status_date_idx ... Heap Fetches: 0There's a condition. PostgreSQL must know that every row on those table pages is visible to everyone, which it tracks in the visibility map. VACUUM keeps that map up to date. On a table that changes a lot and isn't vacuumed, "Heap Fetches" climbs and the benefit fades. Day 24 explains why.
You can make an index cover a query on purpose. A covering index adds extra columns with INCLUDE. They're stored in the index's leaf pages but aren't part of the sort order, so they cost nothing for lookups and let more queries skip the table:
CREATE INDEX orders_customer_date_cov_idx ON orders (customer_id, order_date) INCLUDE (amount);
VACUUM orders;
EXPLAIN ANALYZE SELECT order_date, amount FROM orders WHERE customer_id = 4242;Index Only Scan using orders_customer_date_cov_idx on orders (actual rows=8.00 loops=1)
Heap Fetches: 0
Buffers: shared hit=4Tested on PostgreSQL 18. The same query on the plain (customer_id, order_date) index read 11 pages: 3 in the index and one table page per matching order. The covering version read 4 and never touched the table. The price is size: 15 MB against 11 MB here, plus the extra write on every insert. Add INCLUDE for a few small columns on your hottest queries, not as a habit. The planner also needs the visibility map, so the same VACUUM rule applies.
One last production tip: plain CREATE INDEX blocks writes to the table while it builds. On a live system, use CREATE INDEX CONCURRENTLY. It takes longer but lets writes carry on.
Check yourself
Create an index on email, a unique one if emails are unique, using CREATE INDEX CONCURRENTLY on a live system. Then rerun EXPLAIN ANALYZE to confirm an index scan.
The condition matches almost every row, so reading the whole table is cheaper than using the index. Indexes pay off when they narrow the result to a small share of rows.
The index stores raw email values, so it can't answer a condition on lower(email). Create an expression index on lower(email), or store the email in a consistent case.
Create a covering index, CREATE INDEX ... ON orders (customer_id) INCLUDE (status, total). With the visibility map up to date, PostgreSQL can answer from the index alone with an index-only scan.
Usually not efficiently, because a B-tree index is ordered by its first column. Filters on the leading column, or on both, use it well. PostgreSQL 18 can sometimes skip-scan over a low-cardinality leading column, but for frequent queries on created_at alone, create a separate index.