If you’ve ever added an index and still seen slow queries, you’re not alone.
Most developers understand what indexes are—but not how to design them properly.
This blog breaks down single-column vs composite indexes, the leftmost prefix rule, and the critical role of column order, especially when range queries are involved.
🚀 What is an Index (Quick Recap)
An index is a data structure (usually a B-Tree in PostgreSQL) that allows the database to find rows faster without scanning the entire table.
- Without index → Full table scan (slow)
- With index → Direct lookup (fast, O(log n))
🔹 Single-Column Index
A single-column index is created on one column.
CREATE INDEX idx_user_id ON users(user_id);
✅ When to use
- Queries filter on one column
- Column has high selectivity (e.g., user_id, email)
⚠️ Limitation
- Not helpful for multi-column queries
🔹 Composite Index (Multi-Column Index)
A composite index includes multiple columns in a specific order.
CREATE INDEX idx_user_status ON users(user_id, status);
✅ When to use
- Queries filter on multiple columns together
- Queries involve
WHERE + ORDER BY
🔥 The Most Important Rule: Leftmost Prefix
For a composite index like:
(user_id, status, created_at)
It works for:
- ✅
user_id - ✅
user_id + status - ✅
user_id + status + created_at
But NOT for:
- ❌
status - ❌
created_at - ❌
status + created_at
👉 This is called the leftmost prefix rule.
⚠️ Why Column Order Matters
The order of columns in a composite index is not random.
👉 Always put:
- Most frequently filtered column first
- High selectivity column first
Example
Query:
SELECT * FROM user_exercises
WHERE user_id = 10 AND status = 'pending';
Better index:
(user_id, status)
Bad index:
(status, user_id)
Why?
Because user_id is more selective and usually the primary filter.
📊 Range Queries (Critical Concept)
What are range queries?
Queries using:
>,<,>=,<=BETWEENLIKE 'prefix%'
Example:
WHERE created_at > '2025-01-01'
🔥 Golden Rule for Composite Indexes
Equality conditions first, range condition last
✅ Good Example
Index:
(user_id, created_at)
Query:
WHERE user_id = 10 AND created_at > '2025-01-01'
user_id → exact match (fast jump)
created_at → range scan (efficient)
❌ Bad Example
Index:
(created_at, user_id)
Same query:
WHERE user_id = 10 AND created_at > '2025-01-01'
-
Database starts scanning on created_at
-
Cannot efficiently filter user_id
-
Performance degrades
⚠️ What Happens After a Range Condition?
Once a range condition is applied:
👉 The database starts scanning
👉 Columns after that are not effectively used
Example
Index:
(user_id, created_at, price)
Query:
WHERE user_id = 10
AND created_at > '2025-01-01'
AND price > 100
user_id → used
created_at → used (range)
❌ price → ignored for index filtering
⚖️ Composite vs Multiple Single Indexes
Let’s say you have:
- Index on
user_id - Index on
status
Query:
WHERE user_id = 10 AND status = 'pending'
👉 Database may not combine indexes efficiently
👉 A composite index `(user_id, status)` is better
🚨 Common Mistakes
- Creating indexes without analyzing queries
- Wrong column order in composite index
- Indexing low-cardinality columns (
status,is_active) - Creating too many indexes → slows writes
- Assuming index will always be used
🧠 When PostgreSQL Ignores Your Index
- Low selectivity (too many matching rows)
- Small table (sequential scan is cheaper)
- Wrong index order
- Poor query structure
👉 Always verify using: EXPLAIN ANALYZE
⚡ Bonus: Covering Index
If all required columns are present in the index:
👉 PostgreSQL can perform an index-only scan
👉 No need to access the main table
Example:
(user_id, status, created_at)
🎯 Final Takeaway
- Design indexes based on query patterns, not schema
- Use single-column indexes for independent filters
- Use composite indexes for combined queries
- Follow:
- Leftmost prefix rule
- Equality first, range last