Re: Slow count(*)

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Danny Abraham" <danny_abraham(at)bmc(dot)com>, <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Slow count(*)
Date: 2008-01-03 15:15:59
Message-ID: 477CA7CE.EE98.0025.0@wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

>>> 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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Erik Jones 2008-01-03 15:28:07 Re: Can't make backup
Previous Message Bill Moran 2008-01-03 14:20:31 Re: [OT] Slony (initial) Replication - Slow

Browse pgsql-hackers by date

  From Date Subject
Next Message Usama Dar 2008-01-03 15:27:23 Re: Autovacuum & Table List Ordering
Previous Message Alvaro Herrera 2008-01-03 14:39:26 Re: Autovacuum & Table List Ordering