The list endpoint took 3 seconds. I ran the same query by hand and the rows came back in well under a tenth of that.
The rows were not the problem. The total was.
It was a filtered catalog listing on a marketplace: text match and facets, with a dozen optional filters hanging off it. Postgres served the page of twenty rows straight off an index. Then it ran a second query to count every matching row, so the UI could render "page 7 of 318". That count had no choice but to walk the entire filtered set, on every request, for a number sitting at the bottom of a screen.
I dropped the exact total and moved to keyset pagination. 200ms.
Then the moderation queue broke. Not visibly, since nothing errored. The compliance side had been reading the unmoderated count every morning as their workload signal, and now it showed them nothing useful. That was not written down as a requirement anywhere. It was a number on a screen that someone had quietly built a daily process around.
So the count went back, but only there: refreshed on a schedule, stored, and labelled with how stale it was.
What I do differently now is price a response field by field before I touch anything. Which part of this payload costs a full scan, and who actually reads it. An exact total on a filtered list is usually the most expensive field in the response and the least used one.
I write up more of these on the blog: https://polycratia.com/c/c2dd710e
What is the most expensive single field in your heaviest endpoint?