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

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) SELE