Seconds to load on the marketplace list page, and the first two things I tried were the wrong layer.
It was a P2P lending order book. Every row had to show how much of a loan was still open, and where each investor's pro-rata share stood against it. The endpoint computed all of that live: walk the investments for each deal, sum them, divide, return.
So I did what you do. Read the query plan. Added indexes. Shaved a little off.
Then I reached for a cache and stopped. These numbers are money. A stale remaining amount means two investors can both be told there is room for the same funds. The latency was annoying; that bug would have been expensive, and it would have surfaced as a reconciliation problem weeks later, far away from the endpoint that caused it.
The real change: stop asking the question at read time. The remaining amount became stored state, updated inside the same transaction that records an investment. The read turned into a lookup. Fast, and correct by construction instead of correct by recomputation.
What it cost me was a day spent optimizing an aggregation that had no business being on the read path at all.
What I do differently now: when an endpoint is slow, before I touch the queries I ask whether it is deriving something that only ever changes on write. If it is, the answer is rarely a faster query and almost never a cache.
I write these up in longer form on my blog: https://polycratia.com/c/627d4c37
Somewhere in your read path, I'd guess, something still gets recomputed that a write already knew...