Partial indexes are underrated
A 40 GB table, a query that only ever looks at 4,000 rows of it, and an index that went from 1.9 GB to 38 MB.
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