Indexes are the most powerful performance tool in PostgreSQL and the most misunderstood. The default advice — "add an index to slow columns" — produces tables littered with indexes that the query planner ignores, while the queries that actually hurt stay slow.
What PostgreSQL Actually Does With an Index
The planner isn't obligated to use an index. It estimates the cost of a sequential scan versus an index scan and picks the cheaper one. On a table with 500 rows, a sequential scan is almost always faster — the planner knows this. On a table with 10 million rows returning 0.1% of them, an index is decisive. Understanding this prevents cargo-cult indexing.
B-Tree: The Default and Its Limits
B-tree is the default index type and handles equality, range queries, sorting, and IS NULL. It covers most use cases. What it doesn't handle: case-insensitive searches, array contains, full-text search, JSON key lookups.
-- this uses the index
SELECT * FROM orders WHERE user_id = 42;
-- this does NOT use a btree index on email
SELECT * FROM users WHERE lower(email) = 'user@example.com';For the second query, you need an expression index:
CREATE INDEX idx_users_email_lower ON users (lower(email));Partial Indexes: The Underused Superpower
A partial index only indexes rows matching a WHERE clause. If 95% of your queries against an orders table filter on status = 'pending' and pending orders are 2% of all rows, a partial index is an order of magnitude smaller and faster than a full one.
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';The query must include the same WHERE condition for the planner to use it. Partial indexes also dramatically reduce write amplification — every insert and update only touches the index if the row matches the condition.
Composite Index Column Order
This is where most engineers get it wrong. In a composite index (a, b, c), the planner can use the index for queries filtering on a, on (a, b), or on (a, b, c) — but not on b alone or c alone.
The rule: put the most selective column first, then order remaining columns by how often they appear in query filters. An index on (status, created_at) is useful for "all pending orders sorted by date." An index on (created_at, status) is less useful for that pattern because created_at ranges produce many rows before the status filter applies.
Reading EXPLAIN ANALYZE
Stop guessing. Every slow query deserves an EXPLAIN (ANALYZE, BUFFERS):
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM events WHERE user_id = 42 AND type = 'click'
ORDER BY created_at DESC LIMIT 20;Look for: Seq Scan on large tables (missing index), high rows removed by filter (index exists but isn't selective enough), high actual rows vs estimated rows (stale statistics — run ANALYZE), and nested loop joins on large sets (missing foreign key index).
BUFFERS output shows cache hits vs disk reads. A high ratio of disk reads on a query that should be fast often means the table statistics are stale, not that the index is missing.
Index Bloat and Maintenance
PostgreSQL's MVCC model means deleted and updated rows leave dead tuples behind. Indexes accumulate pointers to these dead tuples over time — this is index bloat. Autovacuum handles most of this, but high-write tables on aggressive update patterns can outpace it.
Check bloat with pgstattuple or pg_stat_user_indexes. An index with idx_scan = 0 after weeks of traffic is dead weight — it slows every write for zero read benefit. Drop it.
When Not to Index
An index is a write tax. Every insert, update, and delete must maintain every index on the table. For append-only tables with heavy bulk inserts, indexes on non-query columns cost throughput for no gain. For small lookup tables under a few thousand rows, the planner will sequential scan regardless. For low-cardinality columns like boolean flags used alone, the index is rarely selective enough to matter.
The right question isn't "should I index this column" — it's "does this specific query pattern on this table's data distribution benefit enough from an index to justify the write cost." EXPLAIN ANALYZE answers that question. Everything else is guessing.