Message-ID: <46BC6342.4010002@janestcapital.com>
Date: Fri, 10 Aug 2007 09:08:18 -0400
From: Brian Hurt <bhurt@janestcapital.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax)
MIME-Version: 1.0
To: runic <runic@gmx.de>
CC: pgsql-performance@postgresql.org
Subject: Re: select count(*) performance
References: <1186578085.034463.116030@q75g2000hsh.googlegroups.com>
In-Reply-To: <1186578085.034463.116030@q75g2000hsh.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

runic wrote:

>Hello Group,
>
>I'm new in PostgreSQL Business, therefore please forgive me a "newbie"
>Question. 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

The problem is that count(*) on a table has to scan the whole table, due 
to the fact that Postgres uses MVCC for it's concurrency control.  This 
is normally a huge win- but one of the few places where it's a loss is 
doing count(*) over a whole table.  In this case, Postgres has no choice 
but to inspect each and every row to see if it's live or not, and thus 
has no choice but to read in the whole table.

If you've been doing a lot of inserts, updates, and/or deletes to the 
table, and you either don't have autovacuum turned on or agressive 
enough, the table can be littered with a bunch of dead rows that haven't 
been deleted yet.  Postgres still has to read in those rows to make sure 
they're dead, so it's easy for it to have to read many multiples of the 
number of live rows in the table.  What vacuum does is it goes through 
and deletes those dead rows.

If that isn't the problem, then it's just that you have to read the 
whole table.  If the rows are large enough, and the disk subsystem is 
slow enough, this can just take a while.  My advice in this case to buy 
either more disks and/or faster disks, to speed up the reading of the table.

Brian