# 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.

- URL: https://personal-example.notra.site/blog/partial-indexes
- Published: 2026-10-08
- Tags: postgres

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:

```sql
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

```sql
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.

> **Note**
>
> The planner only uses a partial index when it can prove the query’s `where` clause implies the index predicate. Write `status = 'pending_payment'` literally. A bind parameter for the status is not enough.

## 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
