We have an orders table with 210 million rows. The support dashboard asks one question all day: which orders are stuck in pending_payment? At any moment that is about 4,000 rows.

The original index covered every row:

create index orders_status_created_idx on orders (status, created_at);

It was 1.9 GB and mostly described orders nobody would ever look at again.

Index only what you query

create index orders_pending_idx on orders (created_at)
  where status = 'pending_payment';

The new index is 38 MB. It fits in memory with plenty of room, and the dashboard query went from 140 ms to under 2 ms.

When I reach for one

  • One status value or flag that a hot query filters on
  • Soft-deleted rows that most queries skip with deleted_at is null
  • Unique rules that only apply to some rows, like one active subscription per customer