Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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

pgsql-hackers by date

Next:From: Usama DarDate: 2008-01-03 15:27:23
Subject: Re: Autovacuum & Table List Ordering
Previous:From: Alvaro HerreraDate: 2008-01-03 14:39:26
Subject: Re: Autovacuum & Table List Ordering

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group