Postgres Indexes That Actually Help

An index is a data structure that lets Postgres find rows without scanning the whole table. But adding indexes blindly wastes disk, slows writes, and often doesn't help at all. Let's build intuition for when they pay off.

Read the plan first

Never guess. Ask Postgres what it's doing:

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
  AND status = 'shipped'
ORDER BY created_at DESC
LIMIT 20;

If you see Seq Scan on a large table for a selective filter, an index is probably missing.

B-tree: the default, and usually right

For equality and range queries, a B-tree covers the common case. A composite index should list columns in order of selectivity and match your query shape:

-- Supports the WHERE + ORDER BY above in one index
CREATE INDEX idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);

The column order matters. This index helps queries filtering on customer_id, or customer_id + status, but not ones filtering on status alone.

Partial indexes for skewed data

If you almost always query one slice of the data, index only that slice:

CREATE INDEX idx_orders_open
  ON orders (created_at)
  WHERE status = 'open';

The index is smaller, faster to scan, and cheaper to maintain.

GIN for arrays, JSONB, and full text

When you're searching inside composite values, reach for GIN:

CREATE INDEX idx_docs_tags ON documents USING GIN (tags);
 
SELECT * FROM documents WHERE tags @> ARRAY['postgres'];

A quick checklist

• Measure with EXPLAIN ANALYZE before and after.
• Match the index to the query's filter and sort.
• Prefer fewer, well-designed composite indexes over many single-column ones.
• Drop indexes that pg_stat_user_indexes shows are never used.

Indexes are a trade: faster reads for slower writes and more storage. Make that trade deliberately, backed by the query planner's own numbers.