Re: count * performance issue

Tom,

> > 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've been thinking that when we apply the Dead Space Map we might be able
to get a similar effect in PostgreSQL. That is, just do a count over the
index, and visit only the heap pages flagged in the DSM. Again, for a
heavily updated table this wouldn't have any benefit, but for most cases
it would be much faster.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco