Re: count * performance issue

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux