From: Luke Lonergan <llonergan@greenplum.com>
To: Gábriel Ákos <akos.gabriel@i-logic.hu>
To: pgsql-performance@postgresql.org
Subject: Re: count(*) performance
Date: Mon, 27 Mar 2006 08:31:58 -0800
Message-ID: <C04D537E.2025E%llonergan@greenplum.com> (raw)
In-Reply-To: <4427E9E8.9030806@i-logic.hu>
Gabriel,
On 3/27/06 5:34 AM, "Gábriel Ákos" <akos.gabriel@i-logic.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: llonergan@greenplum.com, akos.gabriel@i-logic.hu
Subject: Re: count(*) performance
In-Reply-To: <C04D537E.2025E%llonergan@greenplum.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox