Which Postgres Indexes You Actually Need

An index is the first tool anyone reaches for when a query is slow, and adding one is easy. Knowing which Postgres indexes to add — and, just as important, which to remove — is the part that separates a database that scales from one that grinds. Every index speeds up some reads and slows down every write, so an index is never free; it is a trade, and the goal is to make only the trades that pay off.

The trade every index makes

Start from the cost, because it is the thing people forget. An index is a separate data structure the database must update on every INSERT, UPDATE, and DELETE that touches the indexed column. A table with ten indexes pays to maintain ten structures on every write. So indexes are not a pure win — they trade write speed and disk for read speed, and a table drowning in indexes can be slower overall than one with a well-chosen few.

This reframes the whole exercise. The question is never “would an index help this query?” — the answer is usually yes. The question is “does this query matter enough to make every write to this table pay for the index forever?” Most tables need a small handful of well-chosen indexes, not one per column someone once filtered on.

B-tree is the default and it is almost always right

Postgres has several index types, but one does the overwhelming majority of the work. The B-tree is the default, and it handles equality and range queries — =, <, >, BETWEEN, ORDER BY, and prefix matching on text. If you are filtering or sorting on a column, a B-tree is what you want, and it is what you get unless you ask for something else.

The specialised types earn their place in specific situations. A GIN index is for columns holding multiple values that you search inside — full-text search, jsonb containment, array membership. A BRIN index is remarkably small and suits huge tables where the data is naturally ordered on disk, such as an append-only log by timestamp, where it gives most of a B-tree’s benefit at a fraction of the size. These are worth knowing, but do not reach for them by default; the B-tree is right far more often than not, and choosing an exotic index for an ordinary query is a common way to make things slower.

Composite indexes: column order is everything

When a query filters on several columns, a composite index across them can be far faster than separate single-column indexes — but only if the column order matches how you query. This is the single most misunderstood thing about indexing, and getting it wrong produces an index that looks reasonable and never gets used.

A composite index on (a, b, c) can serve queries filtering on a, on a and b, or on all three — it works left to right, like the alphabetical ordering of a phone book. It cannot efficiently serve a query filtering only on b or only on c, because you cannot use a phone book sorted by surname to find everyone with a given first name. This is the “leftmost prefix” rule, and it is the key to composite indexes.

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

-- Uses the index (leftmost prefix satisfied):
WHERE customer_id = 42
WHERE customer_id = 42 AND status = 'open'
WHERE customer_id = 42 AND status = 'open' AND created_at > now() - interval '7 days'

-- Cannot use it efficiently (skips the leftmost column):
WHERE status = 'open'          -- no customer_id filter
WHERE created_at > '2026-01-01'

The practical guidance: order composite columns with the ones used in equality filters first, then the one used in ranges or sorting. Get the order right and one index serves many query shapes; get it wrong and you have paid for an index that the planner quietly ignores while your query does a sequential scan anyway.

Indexes that let the table go untouched

A powerful and underused technique is the covering index — one that includes every column a query needs, so the query is answered entirely from the index without ever reading the table. Postgres calls the result an index-only scan, and it can be dramatically faster because it skips the trip to the heap for each row.

-- If a query reads only customer_id and total, include total in the index
CREATE INDEX idx_orders_covering ON orders (customer_id) INCLUDE (total);
-- Now "SELECT total FROM orders WHERE customer_id = ?" never touches the table

The INCLUDE clause adds columns to the index for retrieval without making them part of the searchable key, which is exactly what you want for a column you select but do not filter on. This is a targeted optimisation for a hot query, not something to apply everywhere, but for the right query it turns two lookups into one. Whether the planner actually chooses it is something you confirm by reading the plan — our guide to reading a Postgres query plan covers spotting an index-only scan and its Heap Fetches figure.

Finding the indexes to delete

The audit nobody runs is for indexes that cost writes and disk while serving no reads. Postgres tracks how often each index is actually used, and the unused ones are pure overhead — slowing every write, consuming disk, and helping no query.

-- Indexes that have essentially never been used
SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan < 50           -- scanned almost never since stats reset
ORDER BY pg_relation_size(indexrelid) DESC;

Run this against production, where the real query patterns live, and after the stats have accumulated for a meaningful period. An index with almost no scans that occupies real space is a candidate for removal — you are paying for it on every write and getting nothing back. Duplicate and redundant indexes hide here too: an index on (a) is redundant when you also have (a, b), because the composite already serves every query the single-column one would. Removing these is the rare performance change that speeds up writes and reclaims disk at the same time.

Indexing less: partial and expression indexes

Two specialised techniques let you build smaller, cheaper indexes that target exactly the queries that matter, and both are underused because most people only know the plain full-column index.

A partial index covers only the rows matching a condition, rather than the whole table. This is powerful when your queries always filter on the same predicate. A common case is a status column that is overwhelmingly one value: if 95% of orders are complete and you only ever query the pending ones, an index over the whole column wastes most of its space and maintenance cost indexing rows you never search. A partial index on just the pending rows is a fraction of the size, faster to maintain, and serves the query just as well.

-- Index only the rows the query actually looks for
CREATE INDEX idx_pending ON orders (created_at) WHERE status = 'pending';
-- Tiny compared to indexing all orders, and the planner uses it
-- whenever a query includes "WHERE status = 'pending'"

An expression index indexes the result of a function rather than a raw column, which solves a specific and frustrating problem: a query that transforms a column cannot use an ordinary index on it. The classic case is case-insensitive search. A query filtering on LOWER(email) cannot use a plain index on email, because the index stores the original values and the query asks about the lowercased ones — so it does a full scan every time. An index on the expression itself fixes it.

CREATE INDEX idx_email_lower ON users (LOWER(email));
-- Now "WHERE LOWER(email) = 'a@b.com'" uses the index instead of scanning

Both techniques share a theme with the whole indexing discipline: index precisely what the query needs, and no more. A partial index says “only these rows are ever searched,” an expression index says “this transformed form is what we search by,” and both produce a smaller, cheaper structure than a naive full-column index while serving the real query better. Reading the query plan confirms the planner actually picks them up — the same verification from our query-plan guide applies, since a partial index the query does not quite match is an index that silently does nothing.

Adding and removing them safely

One operational note that matters at scale: creating an index normally locks the table against writes for the duration, which on a large table means an outage. Postgres offers CREATE INDEX CONCURRENTLY to build without that lock, at the cost of taking longer and needing a validity check afterward — the mechanics and its sharp edges are in our zero-downtime migrations guide. On a busy production table, always reach for the concurrent form.

The whole discipline comes down to a mindset shift: treat indexes as a budget, not a free resource. Add them deliberately for queries that matter, order composite columns to match how you actually filter, and audit periodically for the ones silently taxing every write. A lean set of well-chosen indexes beats a sprawling collection every time — and finding which queries deserve an index in the first place is what a tool like pg_stat_statements is for, covered in our database observability guide.