Database Indexes Explained: B-Trees, Composite Keys, and When Indexes Hurt Performance (2026)

#postgres #database #backend #performance

Database Indexes Explained: B-Trees, Composite Indexes, and When They Hurt Performance

You added an index. The query got slower. Here is why, and how to use indexes correctly.

How B-Tree Indexes Work

A B-Tree index is a sorted data structure that lets the database find rows without scanning the entire table. Think of it like a phone book: sorted by last name, you can find "Smith" without reading every entry.

SELECT * FROM users WHERE email = $1;

CREATE INDEX idx_users_email ON users(email);

Composite Indexes

Order matters. A composite index on (country, city) helps queries filtering by country alone, but NOT queries filtering by city alone.

-- This index helps all three queries below
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

-- Uses index (leftmost prefix)
SELECT * FROM orders WHERE status = 'pending';

-- Uses full index
SELECT * FROM orders WHERE status = 'pending' AND created_at > NOW() - INTERVAL '1 day';

-- Does NOT use index (skips leftmost column)
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '1 day';

When Indexes Hurt

Small tables: Full scan is faster than index lookup for < 1000 rows
High write tables: Every INSERT/UPDATE must also update the index
Low selectivity: Indexing a boolean column (true/false) rarely helps
Too many indexes: Each index costs disk space and slows writes

Partial Indexes

Index only the rows you query:

-- Only index active users (90% of queries)
CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL;

-- Only index recent orders
CREATE INDEX idx_recent_orders ON orders(created_at) WHERE status \!= 'archived';

EXPLAIN ANALYZE

Always verify your index is being used:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
-- Look for: Index Scan using idx_users_email
-- Red flag: Seq Scan (ignoring your index)