From: Luke Lonergan <llonergan@greenplum.com>
To: Andreas Tille <tillea@rki.de>
To: pgsql-performance@postgresql.org
Subject: Re: Performance of count(*)
Date: Thu, 22 Mar 2007 07:46:26 -0700
Message-ID: <C227E4D2.2A1D3%llonergan@greenplum.com> (raw)
In-Reply-To: <Pine.LNX.4.62.0703221239570.13747@wr-linux02>
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
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, tillea@rki.de
Subject: Re: Performance of count(*)
In-Reply-To: <C227E4D2.2A1D3%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