B-tree indexes: composite, covering and ordered

conceptThe concept

A B-tree keeps keys sorted, so it serves =, <, >, BETWEEN, IN, IS NULL, prefix LIKE 'abc%' (with the C collation or text_pattern_ops) and ORDER BY. A composite index on (a, b) is sorted by a, then by b within each a. It is efficient for a = ?, a = ? AND b = ?, a = ? AND b > ? and a = ? ORDER BY b.

The rule of thumb: equality columns first, then the range or sort column. With (customer_id, created_at DESC), "the latest 3 orders of customer 42" is a single index descent that stops after 3 entries: no sort and no scan of older orders.

A covering index includes every column the query needs, enabling an Index Only Scan. INCLUDE (total) adds payload columns that are stored in leaf pages but are not part of the key. Before 18, a filter on only the second column of an index could not use it efficiently; 18 adds skip scan, which probes each distinct value of a low-cardinality leading column.

whyWhy it matters

The right composite index often replaces two or three single-column ones and turns a sort of 100,000 rows into reading 10 index entries. The wrong column order makes the index almost useless for the query you built it for.

Use caseReal-world scenario

The customer page shows "latest orders", the warehouse screen filters by status and date, and the finance export needs id and total for a date range. Each gets one well-shaped index.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE INDEX orders_cust_created_idx ON orders (customer_id, created_at DESC);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 3;
ROLLBACK;

BEGIN;
CREATE INDEX orders_created_cust_idx ON orders (created_at, customer_id);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 3;
ROLLBACK;

BEGIN;
CREATE INDEX orders_created_incl_idx ON orders (created_at) INCLUDE (total, status);
EXPLAIN (ANALYZE, BUFFERS)
SELECT created_at, total
FROM orders
WHERE created_at >= '2025-06-01' AND created_at < '2025-06-08' AND status = 'delivered';
ROLLBACK;

BEGIN;
CREATE INDEX orders_status_created_idx ON orders (status, created_at);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM orders
WHERE created_at >= '2025-06-01' AND created_at < '2025-06-03';
ROLLBACK;

internalsUnder the hood

Every index adds work to every INSERT and to UPDATEs of indexed columns, and it also stops HOT updates when an indexed column changes (see the MVCC lesson). B-tree pages are 8 kB; since 13, duplicate keys are deduplicated, which makes indexes on low-cardinality columns much smaller.

Avoid / preferDo this, not that

CREATE INDEX ON orders (created_at, customer_id);
-- for: WHERE customer_id = $1 ORDER BY created_at DESC

CREATE INDEX ON orders (customer_id, created_at DESC);

CREATE INDEX ON orders (status);
CREATE INDEX ON orders (customer_id);
CREATE INDEX ON orders (created_at);  -- one per column, just in case

-- index the access paths your top queries use, measured from pg_stat_statements

PitfallsCommon mistakes

• Creating one single-column index per column "just in case". The planner can combine them with BitmapAnd, but one composite index matching the query is usually far better, and every index slows writes.
• Putting the range column before the equality column in a composite index.
• Indexing a column that is wrapped in a function in the WHERE clause (lower(email)). Index the expression instead (next lesson).

LimitsWhen not to use it

Do not index columns with few distinct values (booleans, small status sets) on their own; the planner will rarely use them. A partial index on the rare value is usually what you want.

SecuritySecurity notes

Case-insensitive uniqueness matters for logins: with a plain unique index, Ann@x.com and ann@x.com can be two accounts. Use a unique index on lower(email) or a citext column.

ChallengeTry it yourself

BEGIN;
CREATE INDEX orders_cust_status_created ON orders (customer_id, status, created_at DESC);
ANALYZE orders;
SELECT NOT ('Sort' = ANY (pgm.plan_nodes($q$SELECT * FROM orders WHERE customer_id = 1 AND status = 'delivered' ORDER BY created_at DESC LIMIT 10$q$))) AS no_sort,
       pgm.uses_index($q$SELECT * FROM orders WHERE customer_id = 1 AND status = 'delivered' ORDER BY created_at DESC LIMIT 10$q$) AS uses_index;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Multicolumn indexes
• Index-only scans and covering indexes
• Partial indexes