Re: count * performance issue

Craig James wrote:
> Tom Lane wrote:
>> Craig James <craig_james(at)emolecules(dot)com> writes:
>>> 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.
>
> My experience doesn't match this claim. When I ported my application
> from Oracle to Postgres, this was the single biggest performance
> problem. count() in Oracle was always very fast. We're not talking
> about a 20% or 50% difference, we're talking about a small fraction of
> a second (Oracle) versus a minute (Postgres) -- something like two or
> three orders of magnitude.
>

To convince yourself do this in Oracle:

EXPLAIN PLAN FOR SELECT count(*) FROM table_without_any_indexes

and you will see a full table scan. If you add (suitable) indexes you'll
see something like an index full fast scan.

In fact you can make count(*) *very* slow indeed in Oracle, by having an
older session try to count a table that a newer session is modifying and
committing to. The older session's data for the count is reconstructed
from the rollback segments - which is very expensive.

regards

Mark