There is not a lot of specific details here but have you already tried and exhausted things like analyzing execution plans, index optimizations, index encoding, value encoding, denormalization, read replicas, and [front] caching?
In terms of execution plan, the query we are doing is relatively basic even though it includes some aggregation. The aggregation is rule (CASE) based and very simple. It feels like there is no way to quickly (sub-50ms) retrieve information from a database once you reach the high tens of millions of rows.
This technique is a bit advanced, borrowed from hierarchical databases, and optimizes for specific queries known upfront, so it’s cool but not very flexible. There is a lot more to making it work. You can watch [1], if interested.
But I’d also +1 other suggestions here on fine tuning your db engine and just scaling up the server.
[1] https://youtu.be/jzeKPKpucS0
Disclaimer: I’m with AWS.