Postgres Performance: the 80/20 of Indexing
TL;DR: Most slow Postgres queries are fixed by one of four index types. Master those and you'll handle 80% of perf work without touching the planner.
The four indexes that matter
Postgres has many index types. In production, four cover almost every case: B-tree, multi-column, partial, and covering. Master these before reaching for GiST, GIN, or BRIN.
B-tree: the default
Single-column B-tree handles equality (=) and range (<, >, BETWEEN). Plus ORDER BY when the column matches.
CREATE INDEX idx_orders_created_at ON orders (created_at);
-- Now this is fast
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '7 days';Multi-column: the order matters
(a, b, c) serves queries on a, on (a, b), and on (a, b, c). It does NOT serve queries on just b. Put the most selective column first if every query filters on it; otherwise put the most-frequent filter first.
CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);
-- All of these use the index
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';
SELECT * FROM orders WHERE user_id = 1 AND status = 'paid'
ORDER BY created_at DESC;Partial: index a slice
When 99% of rows have one value of a column, indexing the remaining 1% is way cheaper than indexing the whole table — and queries on that slice get sub-millisecond responses.
CREATE INDEX idx_orders_pending
ON orders (created_at)
WHERE status = 'pending';
-- Tiny index, fast queries
SELECT * FROM orders WHERE status = 'pending'
AND created_at < NOW() - INTERVAL '1 hour';Covering: include extra columns
An index that contains every column the query needs lets Postgres skip the heap entirely. Massive wins for read-heavy paths.
CREATE INDEX idx_orders_user_covering
ON orders (user_id) INCLUDE (status, total_cents);
-- Index-only scan, no heap fetch
SELECT status, total_cents FROM orders WHERE user_id = 1;How to know which one you need
EXPLAIN (ANALYZE, BUFFERS) on the slow query. Look for Seq Scan on a large table — that's a missing index. Look for high Buffers: shared read= with low Rows output — that's a covering-index opportunity.
Costs nobody mentions
Each index slows writes (insert/update). Each index uses disk. A 1B-row table with 8 indexes can spend 80% of write time updating indexes. Audit indexes quarterly with pg_stat_user_indexes and drop the ones with idx_scan = 0.