Postgres ships with five index types, and picking the wrong one either does nothing for your query or wastes disk and write throughput for no benefit. Here’s how to pick correctly, not by trial and error.

B-tree: the default, and correct 90% of the time

CREATE INDEX idx_users_email ON users (email);

Use B-tree for equality (=), range (<, >, BETWEEN), and sorting (ORDER BY) on ordinary scalar columns — integers, text, dates, UUIDs. If you’re not sure which index type to reach for, this is the right default. It’s also what Postgres creates automatically for primary keys and unique constraints, so you often already have one without realizing it.

Composite indexes: column order matters

CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

This index helps queries filtering on customer_id alone, or on customer_id AND status together. It does not help a query filtering on status alone — a composite index can only be used left-to-right, like a phone book sorted by last name then first name: useful for “find Smith,” useless for “find everyone named John” regardless of last name.

Put the column with the more selective filter (fewer matching rows per value) first, unless a specific query pattern demands otherwise.

Partial indexes: index only the rows you actually query

CREATE INDEX idx_orders_pending ON orders (customer_id) WHERE status = 'pending';

If 95% of your queries against a large orders table only care about status = 'pending' rows, and pending orders are a small fraction of the table, a partial index is dramatically smaller than a full index over every status — faster to scan, cheaper to maintain, less disk. This is the index type people forget exists, and it’s often the single best fix for “one specific query is slow but the table is huge.”

GIN: for things that aren’t scalar values

CREATE INDEX idx_products_tags ON products USING GIN (tags);
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('english', body));
CREATE INDEX idx_metadata ON events USING GIN (metadata jsonb_path_ops);

GIN indexes handle “does this array/document contain X” queries — array containment (tags @> ARRAY['sale']), full-text search (to_tsvector / @@), and JSONB containment (metadata @> '{"type": "click"}'). A B-tree index cannot do any of this usefully; it’s built for ordering scalar values, not for searching inside a composite structure.

jsonb_path_ops is a smaller, faster-to-write GIN variant if all your JSONB queries use the @> containment operator specifically — check that assumption before you pick it, since it doesn’t support the full range of JSONB operators the default GIN opclass does.

The index you should almost never add: one on every column

Every index speeds up reads and slows down writes — each INSERT/UPDATE/DELETE has to update every index on that table, not just write the row. A table with fifteen indexes “just in case” pays that cost on every write, forever, for indexes that might serve zero real queries.

-- Find indexes that have never been used
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Run this periodically. An index with idx_scan = 0 after weeks of real traffic is pure write overhead with no read benefit — a strong candidate to drop.

Quick decision table

The takeaway

Default to B-tree. Reach for a partial index when a query only ever cares about a slice of the table. Reach for GIN specifically for arrays, JSONB containment, and full-text search — nothing else needs it. And check pg_stat_user_indexes for unused indexes before you add another one on faith.