Indexes

Indexes speed up reads by organizing data for efficient lookup and sorting.

B-tree basics

• Default index type for equality and ORDER BY
• Useful for range queries (>, <, BETWEEN)

CREATE INDEX idx_users_email ON users(email);

Multi-column indexes

Order matters: leftmost columns are most selective.

CREATE INDEX idx_orders_customer_created ON orders(customer_id, created_at DESC);

Supports queries filtering customer_id and ordering by created_at.

Partial/filtered indexes

-- Postgres
CREATE INDEX idx_orders_paid ON orders(created_at) WHERE status = 'paid';

Smaller, targeted indexes for common predicates.

Covering indexes (include)

-- Postgres INCLUDE, SQL Server INCLUDE
CREATE INDEX idx_orders_lookup ON orders(customer_id) INCLUDE (total, status);

Allows queries to be satisfied from the index alone.

Expression and functional indexes

-- Postgres
CREATE INDEX idx_users_email_lower ON users((lower(email)));

Enable case-insensitive lookups.

Maintenance and trade-offs

• Indexes slow down writes (INSERT/UPDATE/DELETE)
• Monitor unused indexes; drop those that don’t pay for themselves
• Reindex or rebuild when fragmentation is severe (engine-dependent)

Summary

• Start with B-tree, add multi-column matching common filters/sorts
• Use partial/covering indexes and expressions to target hot paths