How to Choose the Right Database Index: B-Tree, Hash, and Composite Indexes Compared

Apr 14, 2026

Purpose

I need to speed up my database queries, but there are different index types: B-tree, hash, composite. Which one should I use? And when is a composite index worth the overhead?

Environment

PostgreSQL 14
Orders table: 2 million rows
Customers table: 500,000 rows

Understanding Index Types

Think of indexes like a table of contents in a book. Without an index, the database reads every row to find what you need. With an index, it jumps directly to the right location.

There are three main index types:

┌─────────────────────────────────────────────────────────────────────────┐
│                      Index Type Comparison                               │
├──────────────┬─────────────────────┬─────────────────────┬──────────────┤
│ Index Type   │ Best For            │ Limitations         │ Example Query│
├──────────────┼─────────────────────┼─────────────────────┼──────────────┤
│ B-tree       │ Range queries,      │ Slightly larger     │ WHERE age >  │
│ (default)    │ sorted results      │ than hash           │ 30           │
├──────────────┼─────────────────────┼─────────────────────┼──────────────┤
│ Hash         │ Exact match only    │ Cannot do ranges    │ WHERE id =   │
│              │                     │ or sorting           │ 123          │
├──────────────┼─────────────────────┼─────────────────────┼──────────────┤
│ Composite    │ Multiple column     │ Column order        │ WHERE        │
│              │ filters together    │ matters             │ customer_id  │
│              │                     │                     │ = 5 AND      │
│              │                     │                     │ date > ...   │
└──────────────┴─────────────────────┴─────────────────────┴──────────────┘

B-Tree Index: The Default Choice

B-tree indexes handle both equality and range queries. This is why most databases use B-tree as the default.

CREATE INDEX idx_customer_id ON Orders(CustomerID);

This index helps with:

SELECT * FROM Orders WHERE CustomerID = 123;

SELECT * FROM Orders WHERE CustomerID > 100 AND CustomerID < 200;

SELECT * FROM Orders ORDER BY CustomerID;

Hash Index: Only for Exact Matches

Hash indexes are faster for exact equality lookups, but they can’t help with anything else.

CREATE INDEX idx_session_hash ON Sessions(session_token) USING hash;

This works for:

SELECT * FROM Sessions WHERE session_token = 'abc123def456';

But NOT for:

SELECT * FROM Sessions WHERE session_token > 'abc';
-- Hash index won't help here

Use hash indexes for lookup tables where you only need exact matches, like session tokens or cache keys.

Composite Index: Multiple Columns Together

When your queries filter on multiple columns, a composite index can help. But column order matters.

CREATE INDEX idx_customer_date ON Orders(CustomerID, OrderDate);

I ordered the columns by selectivity - CustomerID has more unique values than OrderDate.

When It Works

SELECT * FROM Orders
WHERE CustomerID = 123 AND OrderDate > '2024-01-01';

The index works because CustomerID is the first column and I’m filtering on it.

When It Doesn’t Work

SELECT * FROM Orders WHERE OrderDate > '2024-01-01';

This query only filters on OrderDate - the second column. The index can’t help efficiently because we’re skipping the first column.

The Leftmost Prefix Rule

A composite index (A, B, C) helps queries that filter on:

WHERE A = ... (uses index)
WHERE A = ... AND B = ... (uses index)
WHERE A = ... AND B = ... AND C = ... (uses index)
WHERE B = ... (cannot use index efficiently - missing A)
WHERE C = ... (cannot use index efficiently - missing A and B)

Common Mistakes

Mistake #1: Over-Indexing Small Tables

Small tables (under 1000 rows) don’t need indexes. The database reads them faster without indexes.

-- Bad idea for a 50-row settings table
CREATE INDEX idx_settings_key ON Settings(key);

Mistake #2: Wrong Column Order

-- OrderDate has fewer unique values - bad first column
CREATE INDEX idx_date_customer ON Orders(OrderDate, CustomerID);

-- Better: CustomerID first (more selective)
CREATE INDEX idx_customer_date ON Orders(CustomerID, OrderDate);

Mistake #3: Indexing Low-Selectivity Columns

Columns with few unique values don’t benefit from indexes:

-- Bad: gender has only 2-3 values
CREATE INDEX idx_users_gender ON Users(gender);

-- Bad: status has only 5 values
CREATE INDEX idx_orders_status ON Orders(status);

Summary Checklist

┌─────────────────────────────────────────────────────────────────┐
│                   Index Selection Guide                          │
├─────────────────────────────────────────────────────────────────┤
│ Query type          → Recommended index                         │
├─────────────────────────────────────────────────────────────────┤
│ Exact match only    → Hash index (if supported)                 │
│ Range queries       → B-tree index                              │
│ Multiple filters    → Composite index (selective column first)  │
│ Small table (&lt;1000) → No index needed                          │
│ Low selectivity     → No index needed                           │
└─────────────────────────────────────────────────────────────────┘

Summary

In this post, I showed how to choose between B-tree, hash, and composite indexes. The key point is: match your index type to your query patterns, and order composite index columns by selectivity.

Final Words + More Resources

My intention with this article was to help others share my knowledge and experience. If you want to contact me, you can contact by email: Email me

Here are also the most important links from this article along with some further resources that will help you in this scope:

👨‍💻 GeeksforGeeks: Indexing in Databases

Oh, and if you found these resources useful, don’t forget to support me by starring the repo on GitHub!