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.

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
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 type | Serves | Does not serve | Size cost |
|---|---|---|---|
| B-tree (default) | =, <, >, BETWEEN, IN, ORDER BY, prefix LIKE 'abc%' | LIKE '%abc', functions on the column, regex | Moderate |
| Hash | = only | Everything else, including ranges and sorting | Small |
| GIN (PostgreSQL) | jsonb, arrays, full-text, pg_trgm | Ordinary scalar ranges | Large |
| GiST / SP-GiST | Geometry, ranges, nearest-neighbour | Plain equality on text | Large |
| Partial | Queries whose predicate implies the filter | Anything outside that predicate | Small |
Covering (INCLUDE) | Index-only scans for listed columns | Writes: every update of an included column | Larger |
| Behaviour | Composite (a, b) | Two separate indexes a, b |
|---|---|---|
WHERE a = ? | Index used | Index on a used |
WHERE a = ? AND b = ? | Both columns matched | Bitmap AND of two indexes |
WHERE b = ? | Not usable | Index on b used |
ORDER BY a, b | No sort needed | Sort required |
Practical edge cases and deep dives
1. Composite column order decides everything
-- 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
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
-- 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));-- Also no index use on a varchar column: the literal is integer
SELECT * FROM orders WHERE order_no = 100234; -- PostgreSQL: 42883, or a castImplicit 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:
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:
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:
-- 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 symptom | Cause | Fix |
|---|---|---|
index row size 3208 exceeds btree maximum 2704 | Indexing a long text/varchar column | Index a hash (md5(col)) or a prefix, or move to a trigram GIN index |
could not create unique index — duplicate key value violates unique constraint | Existing data violates the new uniqueness | Deduplicate first, then create the unique index |
CREATE INDEX CONCURRENTLY fails, index remains INVALID | Build aborted or deadlocked | DROP INDEX and rebuild; check pg_index.indisvalid |
| Sequential scan despite an index | Low selectivity, stale statistics, type mismatch, or function on the column | ANALYZE, rewrite sargably, add an expression index |
LIKE '%term%' never uses the B-tree | Leading wildcard has no prefix to seek | pg_trgm GIN index, or full-text search |
| Query slower after adding an index | Planner picked the new index for a bad case, or write cost rose | Compare EXPLAIN (ANALYZE, BUFFERS) before and after; drop if unused |
ORDER BY a ASC, b DESC forces a sort | Index declared with the wrong mixed directions | Match the index direction to the query |
| Deletes on the parent table stall | Missing index on the foreign key column | Index the referencing column |
| Duplicate indexes found in review | Same leading columns declared twice with different names | Drop one; duplicates cost writes and confuse the planner |
deadlock detected on concurrent upserts | Two transactions take index locks in different orders | Serialise 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 toolsSmall utilities that fit SQL work — they run entirely in this browser tab, so nothing you paste leaves your device.
Related SQL guides
All SQL guidesSQL Cursors vs Set-Based Operations: Syntax, Memory, and Performance
Why a row-by-row cursor costs minutes where one UPDATE takes seconds, how server cursors spool to disk, and the set-based replacements for each loop.
SQL String Functions: CONCAT, SUBSTRING, REPLACE, and Pattern Matching
The concatenation and slice functions that differ between engines, why LIKE 'x%' uses an index and '%x%' cannot, plus escaping and NULL rules.
SQL Date and Time Functions: INTERVAL Arithmetic, Time Zones, and DST
timestamptz versus timestamp, half-open ranges that keep an index, and the daylight saving gaps that make local scheduling wrong once a year.
SQL Row Locking: SELECT FOR UPDATE, SKIP LOCKED, and NOWAIT
How FOR UPDATE locks rows until commit, why the plan decides the lock scope, and the SKIP LOCKED claim-queue pattern that scales to many workers.
More in SQL
All SQL guidesThe guides filed next to this one in SQL, in library order.