Proper indexing can transform query performance from seconds to milliseconds. This guide covers indexing strategies, types, and practical patterns for PostgreSQL and other databases.
How Indexes Work#
Without an index, the database performs a sequential scan:
-- Without index: O(n) full table scan
SELECT * FROM users WHERE email = 'john@example.com';
-- Scans all 1,000,000 rowsWith an index, the database uses a B-tree structure for O(log n) lookups:
-- With index: O(log n) index scan
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'john@example.com';
-- Scans ~20 index pagesIndex Types#
B-Tree Index (Default)#
Best for equality and range queries:
CREATE INDEX idx_users_created_at ON users(created_at);
SELECT * FROM users WHERE created_at > '2024-01-01';
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';GIN Index#
For arrays, JSONB, and full-text search:
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
SELECT * FROM products WHERE metadata @> '{"category": "electronics"}';Partial Indexes#
Index only the rows you need:
CREATE INDEX idx_active_users_email ON users(email)
WHERE deleted_at IS NULL;Covering Indexes#
Include additional columns to avoid table lookups:
CREATE INDEX idx_users_email_covering ON users(email) INCLUDE (name);
SELECT email, name FROM users WHERE email = 'john@example.com';
-- Index-only scan (no heap fetch)Composite Indexes#
Order matters for multi-column indexes:
1CREATE INDEX idx_users_name ON users(last_name, first_name);
2
3-- Uses full index
4SELECT * FROM users WHERE last_name = 'Smith' AND first_name = 'John';
5
6-- Cannot use index efficiently
7SELECT * FROM users WHERE first_name = 'John';Analyzing Index Usage#
1EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';
2
3-- Finding unused indexes
4SELECT indexrelname, idx_scan
5FROM pg_stat_user_indexes
6WHERE idx_scan = 0;Common Anti-Patterns#
- Over-indexing: Too many indexes slow writes
- Low-selectivity columns: Boolean indexes rarely help
- Wrong column order: Composite index order matters
- Functions on columns:
LOWER(email)needs expression index
Conclusion#
Effective indexing requires understanding your query patterns. Start by analyzing slow queries with EXPLAIN ANALYZE, create targeted indexes, and monitor usage.