Re: count * performance issue

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(at)mielke(dot)cc>