Nothing brings a production application to a grinding halt faster than an unindexed database query scanning millions of rows on every HTTP request.
When response times spike from 25ms to 4,000ms, junior developers often panic and start slapping indexes onto every column in sight. But database indexing is not a magic fix-all: every index comes with write overhead, memory consumption, and disk footprint.
Here is a clear, visual explanation of how SQL indexes actually work, how to read an execution plan, and how to design indexes that make queries 100x faster.
1. The Problem: Sequential Scans (Table Scans)
Imagine a telephone book with 10 million names printed in completely random order. If you want to find "John Doe", you have to read the book page by page from the first line to the very end. That is a Sequential Scan (O(N) complexity).
In relational databases (PostgreSQL, MySQL), a sequential scan means the database engine reads every single 8KB disk block into memory, checking if each row matches your WHERE condition. On a 10-million-row table, this can take seconds of heavy I/O.
2. The Solution: The B-Tree Index
When you create an index:
CREATE INDEX idx_users_email ON users(email);
The database creates a balanced search tree (B-Tree) on disk. A B-Tree maintains sorted values in a multi-level hierarchy:
[ Root Node: M ]
/ \
[ Node: D - H ] [ Node: Q - U ]
/ | \ / | \
Leaf Leaf Leaf Leaf Leaf Leaf
(Data) (Data) (Data)
Searching a balanced tree takes O(log N) time. On a table with 10,000,000 rows, a B-Tree lookup requires at most 3 to 4 disk page reads to locate the exact row pointer (ctid). Your query goes from 3,500ms down to 1.2ms.
3. The Power of Composite Indexes & Column Ordering
Queries rarely filter on a single column. Consider this search query:
SELECT id, title, price
FROM products
WHERE category = 'electronics' AND status = 'active'
ORDER BY created_at DESC
LIMIT 20;
If you index category and status separately, the database engine must scan two separate indexes and perform a costly Bitmap Index Scan to intersect the row IDs.
Instead, create a single Composite Index (Multi-column Index):
CREATE INDEX idx_products_cat_status_created
ON products(category, status, created_at DESC);
The Golden Rule of Index Ordering (Equality, Range, Sort - ESR):
- Place columns used in exact equality matches (
WHERE col = 'xyz') first. - Place columns used in sorting (
ORDER BY col DESC) next. - Place range filters (
WHERE price > 100) last.
If your query filters on (status, created_at) but your index starts with category, the index cannot be used because B-Trees can only be traversed from left to right!
4. How to Read EXPLAIN ANALYZE
Never guess whether an index is working. Ask the database directly:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 9842;
Look for these key indicators in the output:
Seq Scan on orders(Bad): The database scanned the entire table. Your index was ignored or doesn't exist.Index Scan using idx_orders_customer_id(Great): The database traversed the B-Tree and fetched matching rows directly from disk blocks.Index Only Scan(Best!): All columns requested inSELECTwere satisfied entirely by the index itself; the database never touched the underlying table data pages.
5. The Hidden Cost of Too Many Indexes
Indexes are not free:
- Write Penalties: Every
INSERT,UPDATE, andDELETEmust rewrite every corresponding index tree. Tables with 15+ indexes suffer massive write slowdowns. - RAM Bloat: Indexes must fit into the database buffer pool (
shared_buffers) to stay fast. Huge unused indexes evict active table pages from memory.
Audit unused indexes in PostgreSQL periodically:
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
Deepen your database skills with our SQL & Databases Roadmap and PostgreSQL Technology Guide.
