How PostgreSQL B-tree indexes work and when to use them

B-tree Indexes

The default PostgreSQL index type is B-tree (balanced tree). It is efficient for equality, range, and sort operations on most data types.

Create an index

-- Single column
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Composite (column order matters!)
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);

-- Covering index (INCLUDE avoids table lookup)
CREATE INDEX idx_users_email_cover
ON users(email) INCLUDE (id, username);

When indexes help

An index speeds up queries with WHERE, JOIN ON, and ORDER BY on the indexed column — but only when the column is selective (few rows match). Indexing a boolean column with 50/50 distribution is rarely useful.

Composite index column order

Put the most selective or equality-filtered column first. An index on (dept_id, salary) can answer queries filtering on dept_id alone or (dept_id, salary) together, but NOT on salary alone.

Index costs

Every write (INSERT, UPDATE, DELETE) must update all indexes on that table. Too many indexes slow down writes and increase storage. Drop unused indexes with DROP INDEX idx_name;. Check usage with pg_stat_user_indexes.