Afaik, now this request
SELECT COUNT(*) FROM user WHERE rating > 50 looks like this:
- find an item with the value of rating 50(or greater) in our b-tree index, pretty fast.
- Iterate though all items which are on the right.
Am I right? If yes, why don't postgres store number of items in each node so we can skip the second step making the request incredibly fast? If no, why are they so slow?
And is there any way to have an index for counts or something to make request as fast as looking for a single item in binary tree?