Re: count(*) performance

Gabriel,

On 3/27/06 5:34 AM, "Gábriel Ákos" <akos(dot)gabriel(at)i-logic(dot)hu> wrote:

> Question: I have a table with 2.5M rows. count(*) on this table is
> running 4 minutes long. (dual opteron, 4gig ram, db on 4 disk raid10
> array (sata, not scsi)) Is this normal? How could I make it run faster?
> Maybe make it run faster for the 2nd time? Which parameters should I
> change in postgresql.conf and how?

Before changing anything with your Postgres configuration, you should check
your hard drive array performance. All select count(*) does is a sequential
scan of your data, and if the table is larger than memory, or if it's the
first time you've scanned it, it is limited by your disk speed.

To test your disk speed, use the following commands and report the times
here:

time bash -c "dd if=/dev/zero of=bigfile bs=8k count=500000 && sync"
time dd if=bigfile of=/dev/null bs=8k

If these are taking a long time, from another session watch the I/O rate
with "vmstat 1" for a while and report that here.

- Luke