Re: Slow count(*)

>>> On Wed, Jan 2, 2008 at 9:29 AM, in message
<BE67D1149BBD5746984545ED91F702E04DCC7A(at)hou-ex-02(dot)adprod(dot)bmc(dot)com>, "Abraham,
Danny" <danny_abraham(at)bmc(dot)com> wrote:

> We are looking for a patch that will help us count using the indexes.

As others have mentioned, that's not currently possible for a count
of all rows in a table, because there can be many versions of a row
under PostgreSQL's MVCC techniques, and the row must currently be
visited to determine whether it is visible in the context of your
database transaction.

> Our product is about 20 times slower on Postgres compared to MS SQL
> Server.
>
> Any ideas?

Again, it is best to show a particular example of a problem, because
you might be making a bad assumption about the cause of your slowness.
If you don't understand MVCC and the need for maintenance, you might
have table bloat which could be the issue. Also, always give the
exact version of PostgreSQL, the OS, and a description of the
hardware.

If you really are doing proper maintenance, and you don't need exact
counts, you might be able to use the approximation stored in the
system tables:

cc=> \timing
Timing is on.
cc=> select count(*) from "Party";
count
--------
135093
(1 row)

Time: 48.626 ms
cc=> select reltuples from pg_class where relname = 'Party';
reltuples
-----------
135091
(1 row)

Time: 9.799 ms

-Kevin