Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2861B9FB5D7 for ; Thu, 22 Mar 2007 07:53:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 52624-05 for ; Thu, 22 Mar 2007 07:53:20 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from fw-bonn.bund.de (mail4-bonn.bund.de [194.95.179.140]) by postgresql.org (Postfix) with ESMTP id 7C6649FB575 for ; Thu, 22 Mar 2007 07:53:20 -0300 (ADT) Received: from m4.mfw.bn.ivbb.bund.de (localhost [127.0.0.1]) by fw-bonn.bund.de (8.13.8/8.13.8) with ESMTP id l2MArI50031322 for ; Thu, 22 Mar 2007 11:53:18 +0100 (CET) Received: (from localhost) by m4.mfw.bn.ivbb.bund.de (MSCAN) id 3/m4.mfw.bn.ivbb.bund.de/smtp-gw/mscan; Thu Mar 22 11:53:18 2007 Date: Thu, 22 Mar 2007 11:53:00 +0100 (CET) From: Andreas Tille X-X-Sender: tillea@wr-linux02 To: pgsql-performance@postgresql.org Subject: Performance of count(*) Message-Id: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-OriginalArrivalTime: 22 Mar 2007 10:54:00.0743 (UTC) FILETIME=[660A3370:01C76C70] X-TM-AS-Product-Ver: SMEX-7.0.0.1499-3.6.1039-15068.002 X-TM-AS-Result: No--2.375600-8.000000-31 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/303 X-Sequence-Number: 23708 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. 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