Message-Id: <46BD9028.EE98.0025.0@wicourts.gov>
Date: Sat, 11 Aug 2007 10:32:08 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "runic" <runic@gmx.de>,"Brian Hurt" <bhurt@janestcapital.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: select count(*) performance
References: <1186578085.034463.116030@q75g2000hsh.googlegroups.com>
	<46BC6342.4010002@janestcapital.com>
In-Reply-To: <46BC6342.4010002@janestcapital.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

>>> On Fri, Aug 10, 2007 at  8:08 AM, in message
<46BC6342.4010002@janestcapital.com>, Brian Hurt <bhurt@janestcapital.com>
wrote:=20
> runic wrote:
>=20
>>I have a table with ca. 1.250.000 Records. When I execute
>>a "select count (*) from table" (with pgAdmin III)  it takes about 40
>>secs.
>>I think that takes much to long. Can you please give me hints, where
>>I can search for Improvements?
>>
>>TIA, Det
>=20
> 1) VACUUM FULL the table, maybe the whole database.
> 2) Buy more/faster hard disks
=20
Det,
=20
Forty seconds is a long time for only 1.25 million rows.  I just ran a =
count
against a production database and it took 2.2 seconds to get a count from =
a
table with over 6.8 million rows.
=20
In addtion to the advice given by Brian, I would recommend:
=20
3)  Make sure you are using a recent version of PostgreSQL.  There have =
been
signiificant performance improvements lately.  If you're not on 8.2.4, I'd
recommend you convert while your problem table is that small.
=20
4)  Make sure you read up on PostgreSQL configuration.  Like many =
products,
PostgreSQL has a default configuration which is designed to start on just
about anything, but which will not perform well without tuning.
=20
5)  Consider whether you need an exact count.  I just selected the =
reltuples
value from pg_class for the table with the 6.8 million rows, and the value =
I
got was only off from the exact count by 0.0003%.  That's close enough for
many purposes, and the run time is negligible.
=20
6)  If you're looking at adding hardware, RAM helps.  It can help a lot.
=20
I'll finish by restating something Brian mentioned.  VACUUM. Use autovacuum=
.
You should also do scheduled VACUUM ANALYZE, under the database superuser
login, on a regular basis.  We do it nightly on most of our databases.
Without proper maintenance, dead space will accumulate and destroy your
performance.
=20
Also, I don't generally recommend VACUUM FULL.  If a table needs agressive
maintenance, I recommend using CLUSTER, followed by an ANALYZE.  It does a
better job of cleaning things up, and is often much faster.
=20
I hope this helps.
=20
-Kevin
=20