The day I sped up a slow Postgres query by 40x

The Production Bottleneck

Once our settlement table crossed 18 million rows, an internal dashboard endpoint listing the 50 most recent failed payment attempts hit a p99 latency of 480 ms. On paper, the table already had a B-Tree index on account_id, which had masked the problem completely against smaller staging datasets.

Here is the exact query running in production:

SELECT id, amount_cents, error_code, created_at
FROM ledger_attempts
WHERE account_id = $1
  AND status = 'FAILED'
ORDER BY created_at DESC
LIMIT 50;

Reading the EXPLAIN ANALYZE Plan

Running EXPLAIN (ANALYZE, BUFFERS) against a replica exposed what the executor was actually doing:

Limit  (cost=41820.44..41820.57 rows=50 width=44) (actual time=479.812..479.826 rows=50 loops=1)
  Buffers: shared hit=3412 read=14890
  ->  Sort  (cost=41820.44..41908.19 rows=35100 width=44) (actual time=479.809..479.818 rows=50 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 31kB
        Buffers: shared hit=3412 read=14890
        ->  Index Scan using idx_attempts_account on ledger_attempts
              (cost=0.43..40654.12 rows=35100 width=44) (actual time=0.084..456.210 rows=34812 loops=1)
              Index Cond: (account_id = 'acc_91f02'::text)
              Filter: ((status)::text = 'FAILED'::text)
              Rows Removed by Filter: 192440
              Buffers: shared hit=3412 read=14890
Planning Time: 0.290 ms
Execution Time: 479.895 ms

Two compounding bottlenecks stood out immediately. First, for high-volume merchants, idx_attempts_account walked roughly 227,000 index entries, visited the table heap to check status = 'FAILED', and discarded 192,440 succeeded rows. Second, because the index had no notion of created_at ordering, PostgreSQL could not stop at 50 rows; it had to materialize all 34,812 matching rows before feeding them into a top-N heapsort. A single dashboard load touched 18,302 8 KB pages (~145 MB).

Choosing the Right Index Shape

A naive composite index on (account_id, status, created_at DESC) would have fixed the query plan, but 91% of rows in ledger_attempts are SUCCEEDED and are never queried through this access path. Indexing every row would waste shared_buffers capacity and penalize write throughput across the hot payment path.

Instead, I built a partial covering index scoped strictly to failed attempts, placing the equality predicate first, the sort column second, and the projected payload in INCLUDE:

CREATE INDEX CONCURRENTLY idx_attempts_failed_recent
ON ledger_attempts (account_id, created_at DESC)
INCLUDE (id, amount_cents, error_code)
WHERE status = 'FAILED';

Because status = 'FAILED' is baked into the index predicate, the index footprint landed at 190 MB rather than 2.4 GB. Walking the B-Tree in (account_id, created_at DESC) order delivers rows pre-sorted, allowing the Limit node to halt after 50 entries.

Measured Impact & Write Trade-Off

The resulting execution plan:

Limit  (cost=0.42..18.95 rows=50 width=44) (actual time=0.062..11.140 rows=50 loops=1)
  Buffers: shared hit=48 read=6
  ->  Index Only Scan using idx_attempts_failed_recent on ledger_attempts
        (cost=0.42..12904.10 rows=34812 width=44) (actual time=0.060..11.124 rows=50 loops=1)
        Index Cond: (account_id = 'acc_91f02'::text)
        Heap Fetches: 4
        Buffers: shared hit=48 read=6
Planning Time: 0.215 ms
Execution Time: 11.178 ms

Page touches dropped from 18,302 to 54, and wall-clock execution fell from 479.9 ms to 11.2 ms. Keeping autovacuum_vacuum_scale_factor tight on this table ensures the visibility map stays fresh so Heap Fetches remains near zero. The explicit trade-off is extra index maintenance whenever a row transitions into FAILED, which represents less than 9% of writes and showed zero measurable impact on insert latency.