Indexes: B-trees, column order and covering

Column order in a composite index

An index on (customer_id, created_at) is sorted by customer first, then by time within each customer. The documentation's rule: equality conditions on leading columns, plus a range condition on the first column without an equality, limit the part of the index that is read.

1CREATE INDEX orders_cust_created_idx ON orders (customer_id, created_at);2 3EXPLAIN ANALYZE4SELECT id, total_cents FROM orders5WHERE customer_id = 42 ORDER BY created_at DESC LIMIT 10;

The plan, with costs left out:

1Limit  (actual time=0.013..0.024 rows=10 loops=1)2  ->  Index Scan Backward using orders_cust_created_idx on orders  (actual time=0.013..0.023 rows=10 loops=1)3        Index Cond: (customer_id = 42)

No sort node: the entries for customer 42 are already in time order, so PostgreSQL walks them backwards and stops after ten. A query filtering only on created_at gets a sequential scan instead, because one day's orders are spread across every customer's section of the index. Put the column you filter by equality first, and the one you sort or range on after it.

Covering indexes and index-only scans

Usually an index scan still visits the table to fetch the other columns. If the index contains everything the query asks for, it need not:

1CREATE INDEX orders_cust_cov_idx ON orders (customer_id, created_at) INCLUDE (total_cents);2 3EXPLAIN ANALYZE SELECT created_at, total_cents FROM orders WHERE customer_id = 42;

Again without costs:

1Index Only Scan using orders_cust_cov_idx on orders  (actual time=0.033..0.034 rows=25 loops=1)2  Index Cond: (customer_id = 42)3  Heap Fetches: 0

INCLUDE columns are stored in the index but not sorted on. There is a catch: the index does not know whether a row version is visible to your transaction, so PostgreSQL checks the table's visibility map, which VACUUM maintains. On a table with many recent changes, Heap Fetches climbs and the advantage shrinks.

When an index is not used

• The condition is not on the indexed expression. WHERE lower(email) = ... needs CREATE INDEX ON customers (lower(email)); WHERE customer_id + 0 = 42 defeats the index on customer_id.
• The condition is not selective. For a value that matches a large share of rows, reading the whole table in order is cheaper.
• The leading column is missing from the condition, as above.
• Types do not match, so a cast is applied to the column rather than to the constant.
• The statistics are stale, so the planner thinks the condition matches far more, or fewer, rows than it does.

The cost side

Each index is written on every insert and on updates that change indexed columns (or that cannot be done as a heap-only update). pg_stat_user_indexes.idx_scan shows indexes that are never scanned; they are candidates for removal. On a busy table, build new indexes with CREATE INDEX CONCURRENTLY, which takes longer but does not block writes.