Pages

Topics

Tools

↑↓navigate↵openescclose
All guides

SQL Indexes: B-Tree, Composite Indexes, and Covering Indexes for Query Performance

Why composite column order decides whether an index is used, how a covering index removes the heap fetch, and which indexes to drop because nothing reads them.

~5 min readSQL
#SQL#Performance#Indexing
3D illustration of a balanced hierarchical B-tree index node tree directing lookup pointers to table disk blocks

Quick Summary

An index is a second, sorted copy of part of a table with pointers back to the rows. B-tree is the default and the one that matters: it answers equality, range, prefix, and ordered scans in O(log n), and it maintains that order on insert, which is the price you pay on writes. Composite indexes sort by their columns left to right, so (tenant_id, created_at) serves WHERE tenant_id = ? ORDER BY created_at, while (created_at, tenant_id) does not serve the same query nearly as well.

A covering index answers a query from the index alone, without touching the table. A partial index indexes only the rows a query actually reads. Both reduce size and I/O, and both are easy to over-apply: every index you add is write amplification on every insert, update, and delete, plus a planner decision that can go wrong.

Basic syntax and query signatures

sql
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC); CREATE UNIQUE INDEX idx_users_email_lower ON users (lower(email)); CREATE INDEX idx_orders_open ON orders (customer_id, created_at) WHERE status = 'open'; CREATE INDEX idx_orders_covering ON orders (customer_id, created_at) INCLUDE (total);

CREATE INDEX CONCURRENTLY (PostgreSQL) builds without blocking writes, at the cost of two table scans and a period where the index is invalid if the build fails; a failed concurrent build leaves an INVALID index behind that still costs writes and never serves reads. Drop and rebuild it. MySQL 8 has ALGORITHM=INPLACE, LOCK=NONE for the same purpose; SQL Server has ONLINE = ON on Enterprise.

The index is only usable if the query's predicate can be matched to its leading columns. EXPLAIN is the only way to know — the plan can flip to a sequential scan on a table with a perfectly good index when the predicate matches a large fraction of rows.

Key comparison table

Index typeServesDoes not serveSize cost
B-tree (default)=, <, >, BETWEEN, IN, ORDER BY, prefix LIKE 'abc%'LIKE '%abc', functions on the column, regexModerate
Hash= onlyEverything else, including ranges and sortingSmall
GIN (PostgreSQL)jsonb, arrays, full-text, pg_trgmOrdinary scalar rangesLarge
GiST / SP-GiSTGeometry, ranges, nearest-neighbourPlain equality on textLarge
PartialQueries whose predicate implies the filterAnything outside that predicateSmall
Covering (INCLUDE)Index-only scans for listed columnsWrites: every update of an included columnLarger
BehaviourComposite (a, b)Two separate indexes a, b
WHERE a = ?Index usedIndex on a used
WHERE a = ? AND b = ?Both columns matchedBitmap AND of two indexes
WHERE b = ?Not usableIndex on b used
ORDER BY a, bNo sort neededSort required

Practical edge cases and deep dives

1. Composite column order decides everything

sql
-- Serves: WHERE tenant_id = 7 ORDER BY created_at DESC CREATE INDEX ON events (tenant_id, created_at DESC); -- Does not serve it well: the sort column leads, so the tenant filter is a scan CREATE INDEX ON events (created_at DESC, tenant_id);

Put equality columns first and the range/sort column last. The reason is mechanical: the index is sorted by the first column, then by the second inside each first-column value. A query pinning tenant_id lands on one contiguous run and reads it in order. A query pinning only created_at across all tenants is fine for the range but cannot filter tenants without touching every entry.

WHERE a = ? AND b > ? AND c = ? wants (a, c, b) — the two equalities in any order, then the range. A range predicate stops the index from being used for the columns after it, which is why placing c last turns the last column into a filter instead of a seek.

2. Covering indexes and the index-only scan

sql
CREATE INDEX idx_orders_cover ON orders (customer_id, created_at) INCLUDE (total, status); SELECT created_at, total, status FROM orders WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 20;

The plan becomes Index Only Scan instead of Index Scan, and the heap fetch disappears. Two caveats. On PostgreSQL the visibility map must be current, so a bulk load without VACUUM turns an "index only" plan into a heap visit per row; EXPLAIN (ANALYZE) shows Heap Fetches. And every INCLUDE column is copied into the index, so an updatable column like status makes index maintenance heavier. Cover the query that runs a thousand times a minute, not every query.

3. Functions and implicit casts defeat the index

sql
-- No index use: the column is wrapped in a function SELECT * FROM users WHERE lower(email) = 'a@example.com'; -- Expression index matches the expression exactly CREATE INDEX idx_users_lower_email ON users (lower(email));
sql
-- Also no index use on a varchar column: the literal is integer SELECT * FROM orders WHERE order_no = 100234; -- PostgreSQL: 42883, or a cast

Implicit casts are the quieter version of the same problem. WHERE bigint_col = '42' is fine because the literal is coerced, but WHERE varchar_col = 42 either errors or casts the column, and a cast on the column means a sequential scan. The same applies to WHERE created_at::date = '2026-01-01'; write created_at >= '2026-01-01' AND created_at < '2026-01-02' instead, which is both sargable and correct across time zones.

4. Selectivity, partial indexes, and indexes nobody uses

An index on a column with three distinct values is usually dead weight: if a query matches 40% of the table, the planner should scan the heap and skip the index. Partial indexes solve the real version of this problem — a queue table where 99% of rows are done:

sql
CREATE INDEX idx_jobs_pending ON jobs (run_at) WHERE state = 'pending'; SELECT * FROM jobs WHERE state = 'pending' AND run_at < now() ORDER BY run_at LIMIT 50;

The index holds only pending rows, so it stays small, stays cached, and updates touch a fraction of the pages. The same predicate must appear in the query for the planner to consider it — WHERE state IN ('pending', 'retry') will not match WHERE state = 'pending'.

Audit for the opposite problem with the statistics views rather than by eye:

sql
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY pg_relation_size(indexrelid) DESC;

An index with zero scans after a full workload cycle is either redundant with another index or a leftover from a query nobody runs any more. Check the calendar first: a reporting query that runs monthly will legitimately show zero.

5. Foreign keys, locks, and missing indexes on the child

PostgreSQL does not index foreign key columns automatically. Without one, deleting a parent row runs a sequential scan of the child table while holding a lock to verify no orphans remain:

sql
-- Deleting one customer scans all 40 million orders, holding a row lock DELETE FROM customers WHERE id = 42; -- A plain index on the referencing column prevents the scan CREATE INDEX ON orders (customer_id);

This shows up as a delete that takes seconds on a busy table and as relation lock waits in pg_locks. MySQL does create the index for you when you declare a foreign key; PostgreSQL and SQL Server (for non-clustered) do not, so it has to be part of the migration.

Common mistakes and runtime errors

Error or symptomCauseFix
index row size 3208 exceeds btree maximum 2704Indexing a long text/varchar columnIndex a hash (md5(col)) or a prefix, or move to a trigram GIN index
could not create unique index — duplicate key value violates unique constraintExisting data violates the new uniquenessDeduplicate first, then create the unique index
CREATE INDEX CONCURRENTLY fails, index remains INVALIDBuild aborted or deadlockedDROP INDEX and rebuild; check pg_index.indisvalid
Sequential scan despite an indexLow selectivity, stale statistics, type mismatch, or function on the columnANALYZE, rewrite sargably, add an expression index
LIKE '%term%' never uses the B-treeLeading wildcard has no prefix to seekpg_trgm GIN index, or full-text search
Query slower after adding an indexPlanner picked the new index for a bad case, or write cost roseCompare EXPLAIN (ANALYZE, BUFFERS) before and after; drop if unused
ORDER BY a ASC, b DESC forces a sortIndex declared with the wrong mixed directionsMatch the index direction to the query
Deletes on the parent table stallMissing index on the foreign key columnIndex the referencing column
Duplicate indexes found in reviewSame leading columns declared twice with different namesDrop one; duplicates cost writes and confuse the planner
deadlock detected on concurrent upsertsTwo transactions take index locks in different ordersSerialise the key order, or retry with backoff

The rule that survives contact with production: index for the queries you have measured, with the column order their predicates imply, and delete the ones the statistics say nobody uses. An index is a promise to maintain sorted data on every write; make the promise only where a read needs it.

Try matching browser tools

All tools

Small utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.

All SQL guides

More in SQL

All SQL guides

The guides filed next to this one in SQL, in library order.