Craig James wrote:
Tom Lane wrote:
Craig James <craig_james@xxxxxxxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
