Composite vs Single-Column Indexes: Optimize Your Query Performance

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:

• >, <, >=, <=
• BETWEEN
• LIKE '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