B-tree index
What is a B-tree index in PostgreSQL?
The B-tree is PostgreSQL's default index type. It handles equality and range comparisons on sortable data and can return rows already sorted.
A B-tree index keeps its keys sorted in a balanced tree. CREATE INDEX builds a B-tree unless you choose another type, and PostgreSQL also uses B-trees for primary keys and unique constraints.
The planner can use a B-tree for comparisons with <, <=, =, >= and >, for BETWEEN and IN, and for IS NULL and IS NOT NULL. It can also return rows in index order, which can save a separate sort for ORDER BY. Every index speeds up some reads and adds work to every write, so index the columns your queries actually filter or sort on.
Example
The example indexes a timestamp column and asks EXPLAIN about the rows from the last hour. The output shows Index Scan using logs_created_at_idx.
CREATE TABLE logs (
id bigint GENERATED ALWAYS AS IDENTITY,
created_at timestamptz NOT NULL
);
INSERT INTO logs (created_at)
SELECT now() - g * interval '1 minute' FROM generate_series(1, 100000) AS g;
CREATE INDEX logs_created_at_idx ON logs (created_at);
ANALYZE logs;
EXPLAIN SELECT * FROM logs WHERE created_at > now() - interval '1 hour';Plain SQL for stock PostgreSQL 16 or 17, no extensions needed. Run it in an empty database you can throw away.
Sources
Official PostgreSQL documentation (in English).