The Postgres indexes you're not using

Most tables I open have the same indexes: the primary key, one on each foreign key, and a couple of single-column B-trees someone added the week a query got slow. They work. But Postgres has a much bigger toolbox, and a few of its tools solve problems a plain B-tree either can't solve or solves expensively.

This post covers the four I reach for most, how to tell when you need them, and how to find the indexes you already have that are doing nothing but slowing your writes.

The running example is a table every backend has some version of — a queue of outbound jobs:

CREATE TABLE sends (
  id          bigserial PRIMARY KEY,
  account_id  bigint      NOT NULL,
  status      text        NOT NULL,        -- 'pending' | 'sent' | 'failed'
  recipient   text        NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now(),
  sent_at     timestamptz
);

Before anything: read the plan

Every decision below starts with EXPLAIN. Not plain EXPLAIN, which shows what Postgres intends to do, but ANALYZE, which runs the query and shows what actually happened:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, recipient FROM sends
WHERE status = 'pending'
ORDER BY created_at
LIMIT 100;

The two things to look at: whether the plan says Seq Scan on a large table, and the Buffers: shared read numbers — how many pages came from disk. An index that's used but still reads thousands of pages isn't the right index yet.

1. Partial indexes: index only the rows you query

The worker that picks up pending sends runs constantly. But pending rows are a tiny slice of the table — almost everything is sent. A normal index on status indexes every row, including millions nobody will ever query by status again.

A partial index covers only the rows that match its WHERE clause:

CREATE INDEX CONCURRENTLY sends_pending_idx
  ON sends (created_at)
  WHERE status = 'pending';

It's small, because it only holds pending rows. It stays small as the table grows, because rows leave the index when they stop being pending. And it's already in created_at order, so the ORDER BY ... LIMIT above reads just the first hundred entries and stops.

The catch: the planner only uses a partial index when it can prove the query's WHERE implies the index's. WHERE status = 'pending' matches. WHERE status = $1 with a parameter usually won't, because at planning time Postgres doesn't know the value. Keep the condition literal in the queries that should hit it.

If you manage schema with Sequelize migrations, partial indexes are supported directly:

await queryInterface.addIndex("sends", ["created_at"], {
  name: "sends_pending_idx",
  where: { status: "pending" },
  concurrently: true,
});

(Concurrent index builds can't run inside a transaction, so run that migration without one.)

2. Covering indexes: answer the query from the index alone

Even with a good index, Postgres normally visits the table itself to fetch the columns you selected. For a hot query that only needs one or two extra columns, that trip is avoidable.

INCLUDE stores extra columns in the index without making them part of the key:

CREATE INDEX CONCURRENTLY sends_account_recent_idx
  ON sends (account_id, created_at DESC)
  INCLUDE (status);

Now "the latest sends for an account, with their status" can be answered as an Index Only Scan — the plan says so explicitly. One caveat: index-only scans rely on the visibility map, so on a table with heavy updates and not much vacuuming, Postgres may still have to check the table. If Heap Fetches in the plan is high, look at autovacuum before you look at the index.

3. Expression indexes: index what you actually compare

This one catches everyone once:

SELECT * FROM users WHERE lower(email) = lower($1);

There's an index on email, and the query still does a sequential scan. The index stores email; the query compares lower(email). To Postgres those are different things.

Index the expression itself:

CREATE INDEX CONCURRENTLY users_email_lower_idx ON users (lower(email));

The expression in the query has to match the index expression exactly. The same trick works for date(created_at), a JSON field extracted with ->>, or any immutable function you filter on.

4. BRIN: tiny indexes for data that arrives in order

created_at on an append-only table has a useful property: rows are physically stored roughly in time order, because they were inserted in time order. A B-tree on that column works, but on a large table it's large too.

A BRIN index stores only the minimum and maximum value for each block range of the table:

CREATE INDEX sends_created_brin ON sends USING brin (created_at);

It's often a tiny fraction of the B-tree's size, and for range queries over large stretches of time — "everything sent last week" — it lets Postgres skip every block range that can't match. It is useless when values are scattered randomly across the table, so it fits events, logs, and time series, not columns that get updated in place.

Order matters in multicolumn indexes

A quick rule that prevents a lot of redundant indexes: an index on (account_id, created_at) serves queries filtering on account_id alone, and on both columns — but not on created_at alone. Put equality filters first and range or sort columns last. And if you have both (account_id) and (account_id, created_at), the first is almost always redundant.

Finding the indexes you should drop

Every index costs something on every insert and update, and takes memory that could hold table data. Postgres tracks how often each one is used:

SELECT relname      AS table,
       indexrelname AS index,
       idx_scan     AS scans,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

An index with zero scans is a candidate for removal — with care:

• The counters only cover the period since statistics were last reset, so check the reset time first, and make sure it spans your monthly jobs and reports.
• On a replica setup, check the replicas too — an index unused on the primary may be what your read traffic depends on.
• Never drop indexes backing a primary key or unique constraint; they enforce correctness, not speed.

Drop with DROP INDEX CONCURRENTLY so you don't lock the table.

The short version

• Start from EXPLAIN (ANALYZE, BUFFERS), not from intuition.
• Query a small, hot subset of rows? Partial index.
• A hot query needs one or two extra columns? Covering index with INCLUDE.
• Filtering on a function of a column? Expression index.
• Huge append-only table queried by time range? BRIN.
• Build and drop with CONCURRENTLY on anything serving traffic.
• Review pg_stat_user_indexes every so often. The best index is sometimes the one you delete.