Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 523FA9FB560 for ; Sat, 23 Jun 2007 12:17:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 87558-02 for ; Sat, 23 Jun 2007 12:17:25 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from raptor.webhostserver.biz (raptor.webhostserver.biz [208.101.26.91]) by postgresql.org (Postfix) with ESMTP id A6E609FB46A for ; Sat, 23 Jun 2007 12:17:25 -0300 (ADT) Received: from 74-134-94-223.dhcp.insightbb.com ([74.134.94.223] helo=[192.168.2.104]) by raptor.webhostserver.biz with esmtp (Exim 4.66) (envelope-from ) id 1I27MZ-0000h1-LG for pgsql-sql@postgresql.org; Sat, 23 Jun 2007 09:17:23 -0600 From: manchicken Organization: notsosoft.net To: pgsql-sql@postgresql.org Subject: Re: Counting all rows Date: Sat, 23 Jun 2007 10:13:32 -0500 User-Agent: KMail/1.9.7 References: <20070623103856.GE7307@KanotixBox> In-Reply-To: <20070623103856.GE7307@KanotixBox> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200706231013.32458.manchicken@notsosoft.net> X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - raptor.webhostserver.biz X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - notsosoft.net X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200706/187 X-Sequence-Number: 28677 On Saturday 23 June 2007 05:38:56 Andreas Kretschmer wrote: > Stefan Arentz schrieb: > > I need to get statistics from a bunch of tables. Simply the number of > > records in them. > > > > The query plan looks like this: > > > > => explain select count(id) from stuff; > > QUERY PLAN > > ------------------------------------------------------------------- > > Aggregate (cost=1629.69..1629.70 rows=1 width=8) > > -> Seq Scan on stuff (cost=0.00..1517.75 rows=44775 width=8) > > > > The sequential scan kind of worries me. Is there a better/faster way to > > do this? > > Yes. > > A "select count(*) from foo;" enforces a seq. scan. Solutions for this > are discussed here: http://www.varlena.com/GeneralBits/120.php > > > Andreas Is there any neat trick for doing this for paging estimates? It looks like the only way to get a result count from a more complex query is to either fetch a count first, or to fetch all records and count them in the application. -- ~ manchicken <>< (A)bort, (R)etry, (I)nfluence with large hammer. 09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0 Please avoid sending me Word or PowerPoint attachments. See http://www.gnu.org/philosophy/no-word-attachments.html