Last spring I spent a week making one reporting query faster, and most of that week went into undoing a single index.

The table was events: about 40 million rows, append-only, one row per user action. One dashboard query ran constantly, and it was the only thing that touched the table in that shape:

SELECT id, kind, payload, created_at
FROM events
WHERE tenant_id = 42
  AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

EXPLAIN (ANALYZE, BUFFERS) said sequential scan, 40 million rows, 3.1 seconds. Tenant 42 owns roughly 6% of the table, so the filter was not very selective, and the sort was the expensive part. Fine — add an index on the column we sort by. That is the advice you find everywhere:

CREATE INDEX events_created_at_idx ON events (created_at DESC);

The next run was 11.9 seconds.

Limit  (cost=0.57..1042.31 rows=50 width=214) (actual time=11873.442..11873.451 rows=50 loops=1)
  ->  Index Scan Backward using events_created_at_idx on events
        (cost=0.57..20845621.00 rows=1000000 width=214) (actual time=11873.438..11873.441 rows=50 loops=1)
        Filter: (tenant_id = 42)
        Rows Removed by Filter: 812344
        Buffers: shared hit=812395
Planning Time: 0.412 ms
Execution Time: 11873.451 ms

The index did exactly what I asked. It walked backwards through time and threw away every row that belonged to another tenant. Tenant 42’s events are sparse and bursty: a few busy days, then nothing for a week. To find 50 matching rows the executor read 812,344 index entries and performed 812,344 heap fetches. Each one was a random page read.

The problem is not the index. The problem is that ORDER BY created_at is only satisfiable by an index whose leading column set matches the equality predicate. An index on (created_at) answers “the newest rows in the whole table”. The query asks “the newest rows for one tenant”. Those are different access paths that happen to share a column.

The index that matches the question:

CREATE INDEX events_tenant_created_idx ON events (tenant_id, created_at DESC);

Limit  (cost=0.57..186.42 rows=50 width=214) (actual time=0.061..0.784 rows=50 loops=1)
  ->  Index Scan using events_tenant_created_idx on events
        (cost=0.57..372841.00 rows=99712 width=214) (actual time=0.058..0.776 rows=50 loops=1)
        Index Cond: ((tenant_id = 42) AND (created_at >= (now() - '7 days'::interval)))
        Buffers: shared hit=61
Planning Time: 0.398 ms
Execution Time: 0.792 ms

Sixty-one buffer hits instead of 812,395. The LIMIT now stops after 50 index entries because the index is already ordered the way the query needs, and every entry it touches belongs to tenant 42.

Two things I should have done before creating anything:

  1. Run the query with SET enable_indexscan = off and compare. If the new index does not beat the old plan, it is not the plan you want — it is a plan you happened to get.
  2. Look at pg_stats.correlation for the columns involved. created_at correlates with physical order almost perfectly (it is an append-only table), which is exactly why the backwards scan looked cheap to the planner. The planner was not wrong about the index; it was wrong about how many rows it would have to skip, and with a LIMIT it cannot know that in advance.

The general rule I now write on the whiteboard: the index has to answer the question in the order the question asks it. Equality predicates first, then the range or sort column. If the query filters on tenant_id and orders by created_at, the index is (tenant_id, created_at) — not two separate indexes, and definitely not (created_at) alone.

The old index stayed for a month because removing it felt risky. It was not risky: pg_stat_user_indexes.idx_scan showed 41 scans in 30 days, all of them from the dashboard query, all of them slow. Dropping it was the second half of the fix.