Afaik, now this request SELECT COUNT(*) FROM user WHERE rating > 50 looks like this:

  1. find an item with the value of rating 50(or greater) in our b-tree index, pretty fast.
  2. 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?

  • How would you store a count in the face of concurrent changes that need to be atomic and isolated? – jjanes May 2 at 19:39
  • @jjanes, use some kind of lock? – devalone May 2 at 21:01
  • A lock for the duration of the transaction? That is going to be a devastating loss of concurrency. – jjanes May 2 at 21:37
  • @jjanes, another option is to get the count as is, despite of sometimes we're going to get wrong values. – devalone May 2 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.