Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 056399FB7AF for ; Thu, 22 Mar 2007 08:48:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 83661-05 for ; Thu, 22 Mar 2007 08:48:15 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from fw-bonn.bund.de (mail2-bonn.bund.de [194.95.179.138]) by postgresql.org (Postfix) with ESMTP id E4CDA9FB6B1 for ; Thu, 22 Mar 2007 08:48:19 -0300 (ADT) Received: from m2.mfw.bn.ivbb.bund.de (localhost [127.0.0.1]) by fw-bonn.bund.de (8.13.8/8.13.8) with ESMTP id l2MBmHuT032541 for ; Thu, 22 Mar 2007 12:48:18 +0100 (CET) Received: (from localhost) by m2.mfw.bn.ivbb.bund.de (MSCAN) id 3/m2.mfw.bn.ivbb.bund.de/smtp-gw/mscan; Thu Mar 22 12:48:17 2007 Date: Thu, 22 Mar 2007 12:48:08 +0100 (CET) From: Andreas Tille X-X-Sender: tillea@wr-linux02 To: pgsql-performance@postgresql.org Subject: Re: Performance of count(*) In-Reply-To: <20070322111044.GC4439@andi-lap.la.revver.com> Message-Id: References: <20070322111044.GC4439@andi-lap.la.revver.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-OriginalArrivalTime: 22 Mar 2007 11:49:09.0388 (UTC) FILETIME=[1A255CC0:01C76C78] X-TM-AS-Product-Ver: SMEX-7.0.0.1499-3.6.1039-15068.002 X-TM-AS-Result: No--7.017900-8.000000-31 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/308 X-Sequence-Number: 23713 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. > 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. > 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, 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. Kind regards Andreas. -- http://fam-tille.de