Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id DB2179FB61E for ; Thu, 22 Mar 2007 08:09:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 82977-08 for ; Thu, 22 Mar 2007 08:09:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from heaven.kostyrka.org (h1634.serverkompetenz.net [81.169.154.44]) by postgresql.org (Postfix) with ESMTP id 003619FB5D7 for ; Thu, 22 Mar 2007 08:09:24 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by heaven.kostyrka.org (Postfix) with ESMTP id ACA634FB8A; Thu, 22 Mar 2007 12:09:22 +0100 (CET) Received: from heaven.kostyrka.org ([127.0.0.1]) by localhost (heaven.kostyrka.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 31601-01; Thu, 22 Mar 2007 12:09:22 +0100 (CET) Received: from andi-lap (localhost [127.0.0.1]) by heaven.kostyrka.org (Postfix) with SMTP id 68C1E4FB88; Thu, 22 Mar 2007 12:09:20 +0100 (CET) Received: by andi-lap (sSMTP sendmail emulation); Thu, 22 Mar 2007 12:10:47 +0100 Date: Thu, 22 Mar 2007 12:10:47 +0100 From: Andreas Kostyrka To: Andreas Tille Cc: pgsql-performance@postgresql.org Subject: Re: Performance of count(*) Message-ID: <20070322111044.GC4439@andi-lap.la.revver.com> References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: mutt-ng/devel-r804 (Debian) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/304 X-Sequence-Number: 23709 * Andreas Tille [070322 12:07]: > Hi, > > I just try to find out why a simple count(*) might last that long. > At first I tried explain, which rather quickly knows how many rows > to check, but the final count is two orders of magnitude slower. Which version of PG? The basic problem is, that explain knows quickly, because it has it's statistics. The select proper, OTOH, has to go through the whole table to make sure which rows are valid for your transaction. That's the reason why PG (check the newest releases, I seem to remember that there has been some aggregate optimizations there), 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. Andreas > > My MS_SQL server using colleague can't believe that. > > $ psql InfluenzaWeb -c 'explain SELECT count(*) from agiraw ;' > QUERY PLAN ----------------------------------------------------------------------- > Aggregate (cost=196969.77..196969.77 rows=1 width=0) > -> Seq Scan on agiraw (cost=0.00..185197.41 rows=4708941 width=0) > (2 rows) > > real 0m0.066s > user 0m0.024s > sys 0m0.008s > > $ psql InfluenzaWeb -c 'SELECT count(*) from agiraw ;' > count --------- > 4708941 > (1 row) > > real 0m4.474s > user 0m0.036s > sys 0m0.004s > > > Any explanation? > > Kind regards > > Andreas. > > -- > http://fam-tille.de > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate