SQL B-tree, Composite, and Covering Indexes

Practical SQL indexing patterns for equality, range filters, ordering, include columns, and covering scans.

View

Export

Copy the compact sheet, download it, or print it.

Download

`D` dense toggle · `C` copy all

## Create index patterns

Create a single-column index

CREATE INDEX idx_users_email ON users (email);

# Index a common lookup column.

Create a composite index

CREATE INDEX idx_orders_status_created_at ON orders (status, created_at DESC);

# Index fields that are commonly used together.

PostgreSQL covering index with INCLUDE

CREATE INDEX idx_orders_status_created_at_inc ON orders (status, created_at DESC) INCLUDE (total_amount, user_id);

# Store extra columns for index-only scans.

Design for equality then range

-- Often effective
INDEX (tenant_id, status, created_at)

-- Query
WHERE tenant_id = ? AND status = ? AND created_at >= ?

# Put equality columns first in many B-tree cases.

## Covering and ordering

Support ORDER BY with an index

SELECT id, created_at
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

# Avoid expensive sorts when possible.

Example covering query

SELECT status, created_at, total_amount
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

# Query only indexed columns when possible.

More in Database Indexing

No recommendations yet.