🧩Composite and covering indexes

Left-prefix seeks, INCLUDE payloads

Composite indexes order columns left to right: the engine can seek on a leading prefix of that order. Covering indexes add enough key or INCLUDE payload so common reads never touch the heap, trading index width and write cost for fewer random I/Os.

SQL

Covering index with INCLUDE (SQL Server style)

CREATE NONCLUSTERED INDEX ix_orders_customer_cover
ON dbo.orders (customer_id)
INCLUDE (order_date, total_usd);

✓Illustrative result set

result
CREATE INDEX

SQL

Query satisfied from index only

SELECT customer_id, order_date, total_usd
FROM orders
WHERE customer_id = 441;

✓Illustrative result set

customer_id	order_date	total_usd
441	2026-04-01	120.5
441	2026-04-08	44

📐Leading column rule

Order matters more than column count.

An index on (a, b) helps filters on a or a, b, but not filters on b alone, unless the engine can apply other tricks you should not count on by default.

Covering every SELECT * path

Indexing (status, created_at) when workloads always filter created_at alone.
Wide covering indexes that duplicate most of the row on write-heavy fact tables.

Prove heap fetches disappeared

Design composite keys: equality columns first, then range, then ORDER BY alignment.
Validate with EXPLAIN that key/RID lookups dropped after covering.
Cap index width, very fat indexes evict useful pages from buffer pools.

Micro-optimization

Justify covering indexes with measured p95 improvements on real data, not speculation, each extra column slows every mutation touching that row.

Maintenance

Where fragmentation matters, schedule rebuild/reorganize (or autovacuum tuning) after bulk loads so plans stay honest.

TL;DR

A composite index orders entries by multiple key columns left-to-right, leading-column selectivity matters; trailing columns help only after earlier keys are constrained. A covering index (SQL Server INCLUDE, or wide PostgreSQL indexes) stores extra columns in leaf pages so common queries can be satisfied with an index-only scan, avoiding heap lookups at the cost of larger indexes and slower writes.