PostgreSQL indexing: which index, on which column, and when not to

Which index type should you use?

In practice, 90% of the indexes on a typical product database are B-tree, and the interesting decisions are about which columns and in what order rather than which type.

Why does composite index column order matter?

PostgreSQL can use a composite index for a query only if the query constrains a leading prefix of its columns.

CREATE INDEX idx_orders ON orders (customer_id, status, created_at);

-- Uses the index fully
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open' ORDER BY created_at DESC;

-- Uses only the customer_id portion
SELECT * FROM orders WHERE customer_id = 42 AND created_at > now() - interval '7 days';

-- Cannot use this index at all
SELECT * FROM orders WHERE status = 'open';

The rule: equality predicates first, in order of selectivity, then the column you range over or sort by last. Getting this wrong is the difference between an index scan and a sequential scan over ten million rows.

When should you use a partial index?

When your queries consistently filter to a small subset. If 98% of orders are completed and every dashboard query looks at open ones, indexing only the open rows produces an index a fiftieth of the size that fits comfortably in memory.

-- Only index the rows the application actually queries
CREATE INDEX idx_open_orders ON orders (created_at)
WHERE status IN ('open', 'processing');

-- Enforce uniqueness only among live records
CREATE UNIQUE INDEX idx_active_email ON users (lower(email))
WHERE deleted_at IS NULL;

How do you know if an index is being used?

-- Indexes that have never been scanned
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelid NOT IN (SELECT conindid FROM pg_constraint)
ORDER BY pg_relation_size(indexrelid) DESC;

1. 1Check pg_stat_user_indexes after a full business cycle — a month, not an afternoon.
2. 2Drop unused indexes: they slow every insert and update and consume cache memory.
3. 3Look for redundancy — an index on (a) is redundant if (a, b) exists.
4. 4Use EXPLAIN (ANALYZE, BUFFERS) to confirm the planner picks the index you expect.
5. 5Create indexes CONCURRENTLY in production so the build does not lock writes.

When does an index not help?

• Low-cardinality columns where a large share of rows match — a sequential scan is genuinely cheaper.
• Small tables that fit in a page or two; the planner will ignore your index and be right.
• Queries with a leading wildcard LIKE '%term' — use a trigram index instead.
• Functions applied to the column in the WHERE clause without a matching expression index.
• Write-heavy tables where the index maintenance cost exceeds the read benefit.

One useful discipline: before adding an index, capture the query's EXPLAIN plan and timing. After adding it, capture them again. If the plan did not change, remove the index — you have added write cost for nothing.