Composite Indexes & Column Order

A composite index covers multiple columns. But here's the catch: column order determines which queries can use it. Get this wrong and your carefully crafted index sits unused.

How Composite Indexes Work

Think of a composite index like a phone book sorted by last name, then first name:

Index on (last_name, first_name):

Adams, Alice     → Row 5
Adams, Bob       → Row 12
Adams, Charlie   → Row 3
Baker, Alice     → Row 8
Baker, David     → Row 1
...

The data is sorted by last_name first, THEN by first_name within each last_name group.

The Leftmost Prefix Rule

A composite index can be used for queries that include a leftmost prefix of its columns.

CREATE INDEX idx_name ON users(last_name, first_name, middle_name);

Column Order Strategy

The order of columns in a composite index should follow these rules:

Rule 1: Equality Columns First

-- Query pattern
WHERE status = 'active' AND created_at > '2024-01-01'

-- Good: equality first, then range
CREATE INDEX idx_orders ON orders(status, created_at);

-- Bad: range first
CREATE INDEX idx_orders ON orders(created_at, status);

Why? After a range condition, the index can't efficiently use subsequent columns.

Rule 2: High Selectivity Columns First (for equality)

When you have multiple equality conditions, put the most selective column first:

-- country has 195 values, city has 10,000 values
-- Query: WHERE country = 'USA' AND city = 'NYC'

-- Better: more selective first
CREATE INDEX idx_location ON users(city, country);
-- Jumps directly to ~0.01% of data (NYC)

-- Worse: less selective first
CREATE INDEX idx_location ON users(country, city);
-- Jumps to ~5% of data (USA), then filters to NYC

Rule 3: Consider Sort Order

If your query includes ORDER BY, put the sort column last (after equality columns):

-- Query pattern
WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10

-- Optimal: supports both filter and sort
CREATE INDEX idx_user_posts ON posts(user_id, created_at DESC);

-- The index is already sorted, no separate sort needed!

Covering Indexes

A covering index includes all columns needed by a query. The database can answer entirely from the index without touching the table.

-- Query
SELECT user_id, email, created_at
FROM users
WHERE status = 'active'
ORDER BY created_at DESC
LIMIT 100;

-- Covering index
CREATE INDEX idx_users_covering ON users(status, created_at DESC, user_id, email);

Index-Only Scan Visualization

Without covering index:
Index lookup → Get row IDs → Fetch rows from table → Return data
              [Fast]         [Slow - random I/O]

With covering index:
Index lookup → Return data directly from index
              [Fast - all data in index]

INCLUDE Clause (PostgreSQL 11+)

For covering indexes, use INCLUDE to add non-searchable columns:

-- Instead of putting email in the index key:
CREATE INDEX idx_users ON users(status, created_at, email);

-- Use INCLUDE for columns only needed in SELECT:
CREATE INDEX idx_users ON users(status, created_at) INCLUDE (email, user_id);

Benefits of INCLUDE:

• Included columns don't affect index ordering
• Can include columns that aren't indexable (like large TEXT)
• Slightly more efficient for the query planner

Real-World Example: E-commerce Orders

-- Common query patterns:
-- 1. List orders for a user, recent first
-- 2. Find orders by status for a user
-- 3. Dashboard: all orders by status

-- Table
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  total_amount DECIMAL(10,2),
  created_at TIMESTAMP DEFAULT NOW()
);

-- Index for queries 1 & 2
CREATE INDEX idx_orders_user ON orders(user_id, status, created_at DESC)
  INCLUDE (total_amount);

-- Index for query 3 (admin dashboard)
CREATE INDEX idx_orders_status ON orders(status, created_at DESC);

Common Mistakes

Mistake 1: Index Per Column

-- Don't do this
CREATE INDEX idx1 ON orders(user_id);
CREATE INDEX idx2 ON orders(status);
CREATE INDEX idx3 ON orders(created_at);

-- For: WHERE user_id = 1 AND status = 'active'
-- PostgreSQL might use bitmap AND, but it's slower than:

CREATE INDEX idx_combined ON orders(user_id, status);

Mistake 2: Wrong Column Order

-- Query: WHERE user_id = ? ORDER BY created_at DESC LIMIT 10

-- Wrong order (extra sort needed)
CREATE INDEX idx ON posts(created_at, user_id);

-- Correct order (no sort needed)
CREATE INDEX idx ON posts(user_id, created_at DESC);

Mistake 3: Redundant Indexes

-- These are redundant:
CREATE INDEX idx1 ON users(email);                    -- Covered by idx2
CREATE INDEX idx2 ON users(email, created_at);        -- Keep this one
CREATE INDEX idx3 ON users(email, created_at, name);  -- Maybe keep if needed

-- idx1 is useless - idx2 can handle all queries idx1 would

Key Takeaways

Next up: Query Optimization - Using EXPLAIN to see how your indexes are actually being used.