Re: count * performance issue

[Mark Mielke <mark@xxxxxxxxxxxxxx>]

Josh Berkus wrote:

Count() on Oracle and MySQL is almost instantaneous, even for very
large tables. So why can't Postgres do what they do?
      

AFAIK the above claim is false for Oracle.  They have the same
transactional issues we do.
    

Nope.  Oracle's MVCC is implemented through rollback segments, rather than 
non-overwriting the way ours is.  So Oracle can just do a count(*) on the 
index, then check the rollback segment for any concurrent 
update/delete/insert activity and adjust the count.  This sucks if there's 
a *lot* of concurrent activity, but in the usual case it's pretty fast

I read the "almost instantaneous" against "the above claim is false" and "Nope.", and I am not sure from the above whether you are saying that Oracle keeps an up-to-date count for the index (which might make it instantaneous?), or whether you are saying it still has to scan the index - which can take time if the index is large (therefore not instantaneous).

Cheers,
mark

-- 
Mark Mielke <mark@xxxxxxxxx>