B-tree, partial and covering indexes in Postgres

What a B-tree gives you

A B-tree keeps keys sorted in pages arranged as a balanced tree. For a table with millions of rows, a lookup usually touches only three or four index pages before reaching the heap. The default operator class handles =, <, >, BETWEEN, IN and IS NULL, and it can satisfy ORDER BY without a sort node when the direction matches. LIKE 'abc%' only uses the index under the C collation or with text_pattern_ops.

CREATE INDEX orders_customer_idx ON orders (customer_id);

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

The planner will still ignore the index if the predicate matches most of the table. That is correct behavior, not a bug.

Column order in multicolumn indexes

A multicolumn B-tree works from the left. Equality conditions belong first, and the range condition or sort column goes last. Consider a query that fetches one customer's recent orders, sorted by time:

CREATE INDEX orders_cust_created_idx ON orders (customer_id, created_at);

SELECT id, created_at
FROM orders
WHERE customer_id = 42
  AND created_at >= now() - interval '30 days'
ORDER BY created_at;

This index walks to the customer, reads a contiguous range of timestamps, and returns rows already sorted. Reverse the columns and Postgres has to scan every recent order for every customer, then filter. Before Postgres 18 there is no way to skip over a leading column that has no restriction. Version 18 added skip scans for some of these cases, but I would still design the index for the query rather than rely on that.

Separate single-column indexes can be combined with a BitmapAnd, but a composite index almost always beats that.

Partial indexes

A partial index covers only the rows matching a predicate. Soft-deleted tables are the classic case:

CREATE INDEX orders_open_customer_idx
    ON orders (customer_id)
    WHERE deleted_at IS NULL;

-- uses the partial index
SELECT id FROM orders
WHERE customer_id = 42 AND deleted_at IS NULL;

-- cannot use it, the planner cannot prove the predicate
SELECT id FROM orders WHERE customer_id = 42;

The query has to imply the index predicate. The planner checks this at plan time, so write the condition literally in the query. Partial unique indexes are useful too. A unique index on users (email) WHERE deleted_at IS NULL lets a new account reuse an address that was soft-deleted earlier.

Covering indexes and index-only scans

INCLUDE adds columns to the leaf pages without making them part of the search key. Those columns are not used for lookups or ordering, only for returning values without visiting the heap:

CREATE INDEX orders_cust_inc_idx
    ON orders (customer_id) INCLUDE (total_cents, status);

EXPLAIN (ANALYZE, BUFFERS)
SELECT total_cents, status FROM orders WHERE customer_id = 42;

When the plan shows Index Only Scan, the heap is skipped only for pages the visibility map marks all-visible. Pages written recently are not all-visible until vacuum runs, so after a bulk load the Heap Fetches: line can be large. I run a manual VACUUM on those tables before I judge the index. Autovacuum usually catches up, but not always on a busy table.

What indexes cost on writes

Every INSERT adds an entry to every index on the table. An UPDATE is cheaper only in the best case. If no indexed column changes and the new tuple fits on the same page, Postgres makes a HOT update and writes no index entries. Otherwise every index gets a new entry. Columns referenced by a partial index predicate count as indexed columns for this purpose, so soft-deleting a row by setting deleted_at is usually not a HOT update when that column appears in a predicate.

Indexes also bloat under heavy updates and deletes. For write-heavy tables I lower fillfactor to leave room for HOT updates, usually somewhere in the 70 to 90 range. Most of all, I remove indexes nobody reads. Statistics show which ones:

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

Check this on the primary and on any replicas before dropping anything, and remember that the counters reset when statistics are reset.