From: Andreas Kostyrka <andreas@kostyrka.org>
To: Andreas Tille <tillea@rki.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance of count(*)
Date: Thu, 22 Mar 2007 13:29:46 +0100
Message-ID: <20070322122944.GD4439@andi-lap.la.revver.com> (raw)
In-Reply-To: <Pine.LNX.4.62.0703221239570.13747@wr-linux02>
References: <Pine.LNX.4.62.0703221149210.13747@wr-linux02>
<20070322111044.GC4439@andi-lap.la.revver.com>
<Pine.LNX.4.62.0703221239570.13747@wr-linux02>
* Andreas Tille <tillea@rki.de> [070322 13:24]:
> On Thu, 22 Mar 2007, Andreas Kostyrka wrote:
>
> >Which version of PG?
>
> Ahh, sorry, forgot that. The issue occurs in Debian (Etch) packaged
> version 7.4.16. I plan to switch soon to 8.1.8.
I'd recommend 8.2 if at all possible :)
>
> >That's the reason why PG (check the newest releases, I seem to
> >remember that there has been some aggregate optimizations there),
>
> I'll verify this once I moved to the new version.
8.1 won't help you I'd guess. ;)
>
> >does
> >a SeqScan for select count(*) from table. btw, depending upon your
> >data, doing a select count(*) from table where user=X will use an
> >Index, but will still need to fetch the rows proper to validate them.
>
> I have an index on three (out of 7 columns) of this table. Is there
> any chance to optimize indexing regarding this.
Well, that depends upon you query pattern. It's an art and a science
at the same time ;)
>
> 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.
Simple. MSSQL is optimized for this case, and uses "older"
datastructures. PG uses a MVCC storage, which is not optimized for
this usecase. It's quite fast for different kinds of queries.
The basic trouble here is that mvcc makes it a little harder to decide
what is valid for your transaction, plus the indexes seems to be
designed for lookup, not for data fetching. (Basically, PG can use
indexes only to locate potential data, but cannot return data directly
out of an index)
Andreas
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: andreas@kostyrka.org, tillea@rki.de
Subject: Re: Performance of count(*)
In-Reply-To: <20070322122944.GD4439@andi-lap.la.revver.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