SQL Index Optimization Tutorial: Composite, Covering, and Partial Indexes

Overview

Indexes are the single biggest lever for query performance. A well-designed index turns a multi-second scan into a millisecond lookup; a poorly chosen one wastes disk space and slows down writes. This tutorial explains how indexes work and how to choose them using real query plans.

How B-Tree Indexes Work

Most relational databases use a B-tree index. It stores keys in sorted order so the engine can find a value in O(log n) instead of scanning every row.

The trade-off is that every index adds write cost. A table with ten indexes writes eleven structures on every insert.

The Query Plan Is the Ground Truth

Never guess which index will help. Ask the database.

-- PostgreSQL
EXPLAIN ANALYZE
SELECT id, title FROM posts WHERE author_id = 42 ORDER BY created_at DESC LIMIT 10;

-- MySQL
EXPLAIN ANALYZE
SELECT id, title FROM posts WHERE author_id = 42 ORDER BY created_at DESC LIMIT 10;

Look for the rows estimate versus actual, and for a Sort node that an index could eliminate.

Single-Column Index

CREATE INDEX idx_posts_author ON posts (author_id);

Good for filtering on one column. Not sufficient when the query filters on several columns, sorts by another, or selects columns not in the index.

Composite Indexes

A composite index covers multiple columns in order. The order matters and must match the query's access pattern.

CREATE INDEX idx_posts_author_created ON posts (author_id, created_at DESC);

This index supports:

-- Filter by author_id, sorted by created_at
SELECT id FROM posts WHERE author_id = 42 ORDER BY created_at DESC LIMIT 10;

-- Filter by author_id only
SELECT id FROM posts WHERE author_id = 42;

It does not support:

-- Filter by created_at only — the leading column is missing
SELECT id FROM posts WHERE created_at > now() - interval '7 days';

The Leftmost Prefix Rule

An index on (a, b, c) can be used for queries on (a), (a, b), and (a, b, c), but not (b), (c), or (b, c) alone.

Equality Before Range

Columns used in equality comparisons should come before columns used in range comparisons.

-- Query
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'paid' AND created_at > '2026-01-01';

-- Correct index order: equality columns first, then range
CREATE INDEX idx_orders_lookup ON orders (customer_id, status, created_at);

If the range column comes first, the database cannot use the trailing equality column efficiently.

Covering Indexes

A covering index includes all columns the query needs, so the engine never touches the table.

-- Query
SELECT author_id, created_at, title FROM posts WHERE author_id = 42;

-- Covering index
CREATE INDEX idx_posts_covering ON posts (author_id, created_at) INCLUDE (title);

PostgreSQL and SQL Server support INCLUDE for non-key columns. MySQL achieves the same effect by adding the columns to the key list, though that increases index size.

Check the plan for Index Only Scan (PostgreSQL) or Using index (MySQL) to confirm the table was skipped.

Partial Indexes

Index only the rows you actually query. Smaller index, faster writes.

-- Only 2% of orders are pending
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

This index is used only when the query includes the same predicate:

SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

Partial indexes are especially effective for soft-delete patterns:

CREATE INDEX idx_users_active ON users (email) WHERE deleted_at IS NULL;

Expression Indexes

When a query filters on a function of a column, a plain index on the column is not used. Index the expression instead.

-- Query
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- Expression index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

Alternatively, store the normalized value in a separate column and index that. It is often simpler and more predictable.

Unique Indexes

CREATE UNIQUE INDEX idx_users_email ON users (email);

A unique index enforces data integrity and doubles as a lookup index. Prefer it over a separate UNIQUE constraint plus a plain index.

What Kills Index Usage

Index Bloat and Maintenance

Frequent updates and deletes leave dead entries in the index. Over time, the index grows and performance degrades.

-- PostgreSQL: check bloat
SELECT
  schemaname, relname AS table_name, indexrelname AS index_name,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- Rebuild
REINDEX INDEX CONCURRENTLY idx_posts_author;

Use CONCURRENTLY to avoid locking the table. Monitor index usage with pg_stat_user_indexes.idx_scan; an index with zero scans is dead weight.

Design Process

1. Identify the slowest queries from pg_stat_statements or the slow query log.
2. Run EXPLAIN ANALYZE on each to find the bottleneck.
3. Design a composite index where equality columns come first, then the range or sort column.
4. Add INCLUDE columns if the query is read-heavy and can be fully covered.
5. Consider a partial index if the query targets a small subset of rows.
6. Re-run EXPLAIN ANALYZE to confirm the plan changed.
7. Drop indexes that show zero usage after a representative period.

Anti-Patterns

• Indexing every column. Slows writes and confuses the optimizer.
• Duplicate indexes. (a) and (a, b) overlap; the single-column index is often redundant.
• Leading range columns in a composite index. Prevents using trailing equality columns.
• Relying on index hints. Fix the query or the index instead.
• Adding an index without measuring. Always compare plans before and after.