Re: select count(*) performance

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