Speed up your queries — the right index can turn a 30-second query into a millisecond lookup.
B-Tree Index — The default, ideal for equality and range queries
Composite Index — Covers multiple columns for multi-condition lookups
Unique Index — Enforces uniqueness while providing fast access
An index is like a book's table of contents — it tells the database where to look without scanning every page.
What Is an Index?
-- Create a basic index on a single column
CREATE INDEX idx_employees_last_name ON employees(last_name);
-- Query that benefits from the index
SELECT * FROM employees WHERE last_name = 'Smith';
How Indexes Work
Without an index, the database performs a full table scan — reading every row to find matches. With an index, it traverses the tree directly to the matching rows.
Operation
Without Index
With Index
Find one row by ID
O(n) — scan all rows
O(log n) — tree traversal
Range query (BETWEEN)
O(n) — scan all rows
O(log n) — find range start, then scan leaves
COUNT with WHERE
O(n) — scan all rows
O(log n) if index covers the filter
INSERT
O(1) — append row
O(log n) — update the tree
UPDATE indexed column
O(1) — update row
O(log n) — rebuild tree node
Types of Indexes
Single-Column Index
CREATE INDEX idx_employees_email ON employees(email);
Composite Index (Multi-Column)
-- Order matters: leftmost prefix rule
CREATE INDEX idx_employees_dept_salary ON employees(department_id, salary);
Query
Uses Index?
Why
WHERE department_id = 5
✅ Yes
Matches leftmost column
WHERE department_id = 5 AND salary > 70000
✅ Yes
Matches both columns
WHERE salary > 70000
❌ No
Skips leftmost column
WHERE department_id = 5 OR salary > 70000
⚠️ Partial
OR may prevent index use
Unique Index
-- Enforces uniqueness and provides fast lookups
CREATE UNIQUE INDEX idx_employees_email ON employees(email);
Partial Index (PostgreSQL)
-- Index only active employees
CREATE INDEX idx_active_employees ON employees(last_name)
WHERE status = 'Active';
Covering Index
-- Include columns needed by the query to avoid table lookups
CREATE INDEX idx_emp_covering ON employees(department_id, salary, first_name, last_name);
-- This query is fully served by the index — no table access needed
SELECT first_name, last_name
FROM employees
WHERE department_id = 5 AND salary > 70000;
Expression Index
-- Index on a function or expression
CREATE INDEX idx_emp_lower_email ON employees(LOWER(email));
-- Uses the expression index
SELECT * FROM employees WHERE LOWER(email) = 'john@example.com';
Creating and Managing Indexes
-- Create index
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- Create index only if it doesn't exist (PostgreSQL)
CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id);
-- Drop an index
DROP INDEX idx_orders_customer;
-- Rename an index (PostgreSQL)
ALTER INDEX idx_orders_customer RENAME TO idx_cust_orders;
-- List all indexes on a table (PostgreSQL)
SELECT
indexname,
indexdef
FROM pg_indexes
WHERE tablename = 'employees';
-- List indexes (MySQL)
SHOW INDEX FROM employees;
When to Create an Index
Scenario
Create Index?
Why
Column in WHERE clause (equality)
✅ Yes
Fast lookup for exact matches
Column in WHERE clause (range)
✅ Yes
B-Tree supports range scans efficiently
Column in JOIN (foreign key)
✅ Yes
Speeds up join operations
Column in ORDER BY
✅ Yes
Avoids costly sort operations
Low-cardinality column (e.g., gender)
⚠️ Maybe
Index may not help if it returns >20% of rows
Small table (<1000 rows)
❌ Usually no
Full scan is fast enough
Column rarely used in queries
❌ No
Wasted space and write overhead
Index Impact on Performance
Operation
Impact of Index
SELECT with WHERE on indexed column
✅ Dramatically faster
SELECT with JOIN on indexed column
✅ Significantly faster
SELECT with ORDER BY on indexed column
✅ Avoids sorting step
INSERT on indexed table
⚠️ Slightly slower (index update)
UPDATE on indexed column
⚠️ Slower (index rebuild)
DELETE on indexed table
⚠️ Slightly slower (index cleanup)
Disk space
⚠️ Extra storage per index
Index Monitoring
-- PostgreSQL: Check index usage statistics
SELECT
schemaname,
tablename,
indexname,
idx_scan AS times_used,
idx_tup_read AS rows_read,
idx_tup_fetch AS rows_fetched,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE tablename = 'employees'
ORDER BY idx_scan DESC;
-- PostgreSQL: Find unused indexes
SELECT
indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
-- Check if an index is being used (PostgreSQL)
EXPLAIN ANALYZE
SELECT * FROM employees WHERE last_name = 'Smith';
-- Look for "Index Scan" vs "Seq Scan" in the output