Understanding Database Indexes: A Guide to Performance Optimization

Indexes are data structures that improve the speed of data retrieval on a database table. Think of them like the index in a book — they help you find information without scanning every page.

Types of Indexes in PostgreSQL

70

B-Tree

10

Hash

8

GIN

7

GiST

5

BRIN

Creating Indexes

-- Single-column index
CREATE INDEX idx_email ON users(email);

-- Composite index
CREATE INDEX idx_name_email ON users(last_name, first_name);

-- Unique index
CREATE UNIQUE INDEX idx_username ON users(username);

-- Partial index — only index active users
CREATE INDEX idx_active_email ON users(email)
WHERE is_active = true;

Performance Impact

Operation	Without Index	With Index	Speedup
SELECT by email	250 ms	2 ms	125× faster
JOIN on foreign key	1.2 s	15 ms	80× faster
ORDER BY indexed col	180 ms	5 ms	36× faster

When TO Use Indexes

• Columns frequently used in WHERE clauses
• Foreign key columns
• Columns used in JOIN operations
• Columns used for sorting (ORDER BY)

When NOT to Use Indexes

• Small tables — the overhead can exceed the benefit
• Frequently updated columns — indexes slow INSERT/UPDATE
• Low-cardinality columns — a boolean column indexed is often slower than a seq-scan
• Write-heavy tables — every write must update all associated indexes

The rule of thumb: add an index when a query’s EXPLAIN ANALYZE shows a Seq Scan on a large table that you query by that column repeatedly.