>>> On Fri, Aug 10, 2007 at 8:08 AM, in message
<46BC6342.4010002@janestcapital.com>, Brian Hurt <bhurt@janestcapital.com>
wrote:
> runic wrote:
>
>>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
>
> 1) VACUUM FULL the table, maybe the whole database.
> 2) Buy more/faster hard disks
Det,
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.
In addtion to the advice given by Brian, I would recommend:
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.
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.
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.
6) If you're looking at adding hardware, RAM helps. It can help a lot.
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.
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.
I hope this helps.
-Kevin