Should this index ship?

The situation

Your product has an assets page. For each customer organization it lists the 50 newest active assets:

SELECT id, name, created_at
FROM assets
WHERE org_id = $1 AND status = 'active'
ORDER BY created_at DESC
LIMIT 50;

The assets table has 2 million rows from 1,000 organizations and is written to all day. The largest customer owns 10% of the rows; most own a fraction of a percent. About 95% of assets are active. Right now the table has no indexes besides the ones this change adds, and the page is slow.

An AI assistant opened a pull request to fix it.

-- migrations/20261018_add_asset_indexes.sql
CREATE INDEX idx_assets_org_id     ON assets (org_id);
CREATE INDEX idx_assets_created_at ON assets (created_at);
CREATE INDEX idx_assets_status     ON assets (status);

CREATE INDEX CONCURRENTLY idx_assets_org_created ON assets (org_id, created_at);

How to decide on an index

Start from the query, not the columns. Write down the queries the index is for. “Index every column in the WHERE clause” is how you end up with this pull request.

Prefer one index shaped like the query, unless different queries need different leading columns. Put the columns compared with = first, then the column used for a range or for ORDER BY. Equality on the leading columns plus a range or order on the next column lets PostgreSQL read one contiguous slice of the index.

Prefer no index on a column most rows share, unless a query needs the rare values. An index on status where 95% of rows are active costs a write on every insert and almost never wins a plan. Indexes that cover only the rare rows come later, in PostgreSQL index types.

Build with CONCURRENTLY on any table that takes writes, unless the table is new or empty. Then check the result: if the build failed, drop the INVALID index and run it again.

Test with your skew. The pull request’s indexes were fine for the biggest customer, but for a small one they read about 1,200 times more pages than the composite index. Always try the extremes.

Check afterwards that something uses it. Look at idx_scan in pg_stat_user_indexes after the index has seen real traffic, and drop indexes that stay at zero.

Reproduce the evidence

The numbers in the review come from this setup on PostgreSQL 18.3. Run it on a throwaway database to see the plans yourself.

CREATE TABLE assets (id bigint, org_id int, created_at timestamptz, status text, name text);
INSERT INTO assets
SELECT g,
       CASE WHEN g % 10 = 0 THEN 1 ELSE 2 + (g % 999) END,      -- org 1 owns 10% of rows
       timestamptz '2026-01-01' + random() * interval '270 days',
       CASE WHEN random() < 0.95 THEN 'active' ELSE 'archived' END,
       'asset-' || g
FROM generate_series(1, 2000000) g;
VACUUM ANALYZE assets;

-- The pull request's indexes:
CREATE INDEX idx_assets_org_id     ON assets (org_id);
CREATE INDEX idx_assets_created_at ON assets (created_at);
CREATE INDEX idx_assets_status     ON assets (status);
ANALYZE assets;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name, created_at FROM assets
WHERE org_id = 500 AND status = 'active' ORDER BY created_at DESC LIMIT 50;

SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes WHERE relname = 'assets';

-- The alternative:
DROP INDEX idx_assets_org_id, idx_assets_created_at, idx_assets_status;
CREATE INDEX idx_assets_org_created ON assets (org_id, created_at);
ANALYZE assets;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name, created_at FROM assets
WHERE org_id = 500 AND status = 'active' ORDER BY created_at DESC LIMIT 50;

The statistics view updates shortly after each query, so run the pg_stat_user_indexes query a second or two after the EXPLAIN. Timestamps and statuses are random, so your page counts will differ a little; the gap between the two plans will be the same order of magnitude.

Check yourself

1. A query filters on org_id = $1 and sorts by created_at DESC LIMIT 50. Why does one index on (org_id, created_at) beat separate indexes on each column?

   Show answer

   Inside one org_id, the composite index’s entries are already sorted by created_at, so PostgreSQL jumps to that organization and reads the newest 50 entries. With separate indexes it can follow only one: walking created_at means discarding every other organization’s rows until 50 match.

   I could explain this without looking

2. Why did the pull request’s indexes look fine for the largest customer and terrible for a small one?

   Show answer

   Walking created_at from the newest finds 50 rows quickly when the organization owns 10% of the table, but has to pass tens of thousands of other organizations’ rows when it owns 0.1%.

   I could explain this without looking

3. What happens to writes during a plain CREATE INDEX, and what do you give up with CONCURRENTLY?

   Show answer

   A plain build takes a SHARE lock, so inserts, updates and deletes wait until it finishes. CONCURRENTLY keeps writes flowing but does more total work, takes longer, cannot run in a transaction block, and can leave an INVALID index behind that you must drop and rebuild.

   I could explain this without looking

4. How do you find out whether an index is earning its keep?

   Show answer

   Check idx_scan in pg_stat_user_indexes after a period of real traffic. An index that stays at zero is pure write and storage cost.

   I could explain this without looking