Don’t guess the index. Run the loop.
Rank queries by total time, read the plan’s pages, test one hypothesis, then verify reads and writes.
Which query do you fix first?
Bad: The slowest single run
A 30-second daily report barely registers.
Good: Most total time (calls × mean)
4 ms × 2,000 calls/s = 8 seconds of database time every second.
Index only the 4,000 rows the query wants
WHERE status = 'pending' on the index: ≈ 100 KB, not ≈ 1.1 GB, already sorted by created_at. No Sort, no status filter.
7,700×
fewer pages read, after one partial index
909,088 buffers, about 7 GB, became 118. Each run fell from 4.1 s to 0.39 ms; the daily bill from 19.7 hours to 7 seconds.
The catch: status is now an indexed column
Before, a status change could be HOT and write no index entries. Now every one adds an entry to each other index.
There’s an index on email, yet WHERE lower(email) = … still gets a Seq Scan.
Why
It orders email, not lower(email). A function, cast or leading % hides the column: index the expression, or rewrite to the bare column.
Match the index to the query’s shape
- Hot subset, like pending orders → partial index
LIKE '%paneer%'→ trigram GIN- JSONB containment or arrays → GIN
- Append-only time series → BRIN, min–max per block range
Remember
Measure, don’t guess: rank queries by total time, read the plan’s buffers, build the narrowest index concurrently, then check the write side. Prune unused indexes; key with sequences or UUIDv7.