You don't need to be a DBA to understand indexing. Knowing the basics helps you design better APIs and debug performance issues.
What is an Index?
An index is like a book's table of contents. Instead of scanning every row (full table scan), the database jumps directly to matching rows.
-- Without index: scans all 1M rows
SELECT * FROM users WHERE email = 'rahul@google.com';
-- Time: ~500ms
-- With index: looks up in B-tree
CREATE INDEX idx_users_email ON users(email);
-- Time: ~1msTypes of Indexes
B-Tree (Default)
Balanced tree structure. Great for equality and range queries. The most common index type.
-- Good for: =, <, >, <=, >=, BETWEEN, LIKE 'prefix%'
CREATE INDEX idx_created ON orders(created_at);
SELECT * FROM orders WHERE created_at > '2024-01-01';Hash Index
Only for equality comparisons. Faster than B-tree for exact lookups but can't do ranges.
GIN (Generalized Inverted Index)
For array and full-text search columns.
CREATE INDEX idx_tags ON posts USING GIN(tags);
SELECT * FROM posts WHERE tags @> ARRAY['javascript'];Composite Index
-- Order matters! Leftmost prefix rule
CREATE INDEX idx_user_status ON orders(user_id, status);
-- Uses index: WHERE user_id = 1 AND status = 'active'
-- Uses index: WHERE user_id = 1
-- DOESN'T use index: WHERE status = 'active' (missing leftmost column)When to Index
- Columns in WHERE clauses
- Columns in JOIN conditions
- Columns in ORDER BY
- Foreign key columns
When NOT to Index
- Small tables (< 1000 rows)
- Columns with low cardinality (boolean, status with 3 values)
- Tables with heavy write operations (indexes slow down inserts)
- Columns rarely used in queries
Query Analysis
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'rahul@google.com';
-- Look for:
-- "Seq Scan" → No index being used (bad for large tables)
-- "Index Scan" → Index being used (good)
-- "Bitmap Index Scan" → Multiple index conditions combined