User-Agent: Microsoft-Entourage/11.3.3.061214
Date: Thu, 22 Mar 2007 07:46:26 -0700
Subject: Re: Performance of count(*)
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Andreas Tille" <tillea@rki.de>,
	pgsql-performance@postgresql.org
Message-ID: <C227E4D2.2A1D3%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Performance of count(*)
Thread-Index: AcdskN4PHFbQKNiEEdujTgAX8s8Cag==
In-Reply-To: <Pine.LNX.4.62.0703221239570.13747@wr-linux02>
MIME-Version: 1.0
Content-Type: text/plain;
 charset=us-ascii
Content-Transfer-Encoding: 7bit

Andreas,

On 3/22/07 4:48 AM, "Andreas Tille" <tillea@rki.de> wrote:

> Well, to be honest I'm not really interested in the performance of
> count(*).  I was just discussing general performance issues on the
> phone line and when my colleague asked me about the size of the
> database he just wonderd why this takes so long for a job his
> MS-SQL server is much faster.  So in principle I was just asking
> a first question that is easy to ask.  Perhaps I come up with
> more difficult optimisation questions.

This may be the clue you needed - in Postgres SELECT COUNT(*) is an
approximate way to measure the speed of your disk setup (up to about
1,200MB/s).  Given that you are having performance problems, it may be that
your disk layout is either:
- slow by design
- malfunctioning

If this is the case, then any of your queries that require a full table scan
will be affected.

You should check your sequential disk performance using the following:

time bash -c "dd if=/dev/zero of=/your_file_system/bigfile bs=8k
count=(your_memory_size_in_KB*2/8) && sync"
time dd if=/your_file_system/bigfile of=/dev/null bs=8k

Report those times here and we can help you with it.

- Luke