SQL Indexes: B-Tree, Covering, and Partial Indexes

SQL Indexes: B-Tree, Covering, and Partial Indexes

Indexes are data structures that dramatically speed up data retrieval at the cost of additional storage and slower write operations. Choosing the right indexes is one of the most impactful performance optimizations.

B-Tree Indexes (Default)

CREATE INDEX idx_users_email ON users(email);

B-tree indexes store data in a sorted tree structure, enabling efficient lookups, range scans, and sorting. They are the default index type in most databases.

Best for: Equality (=), range (<, >, BETWEEN), sorting (ORDER BY), and prefix matching (LIKE 'abc%').

Composite (Multi-Column) Indexes

CREATE INDEX idx_orders_status_date ON orders(status, order_date);

Column order matters. The index is useful for queries filtering on status, or status + order_date, but not order_date alone (leftmost prefix rule).

Covering Indexes

A covering index includes all columns needed by a query, allowing the database to answer entirely from the index without accessing the table:

CREATE INDEX idx_orders_covering
ON orders(customer_id, order_date, total);

-- This query is fully covered:
SELECT order_date, total FROM orders WHERE customer_id = 42;

Partial (Filtered) Indexes

Index only a subset of rows matching a condition:

CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

Partial indexes are smaller and faster when queries consistently filter on the same condition.

When to Create Indexes

• Columns frequently used in WHERE, JOIN, and ORDER BY clauses
• Foreign key columns (often missed but critical for JOIN performance)
• Columns with high cardinality (many distinct values)

When NOT to Index

• Small tables (full scan is faster than index lookup)
• Columns with low cardinality (e.g., boolean flags with few distinct values)
• Tables with heavy write workloads where index maintenance outweighs read benefits
• Columns rarely used in queries

Monitoring Index Usage

-- PostgreSQL: find unused indexes
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

Regularly audit indexes to remove unused ones that waste storage and slow down writes. A well-chosen set of indexes is the difference between millisecond and minute-long queries.