X-Original-To: pgsql-docs-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 510AF5346E; Thu, 24 Mar 2005 17:01:45 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 93394-06; Thu, 24 Mar 2005 17:01:42 +0000 (GMT) Received: from fetter.org (dsl092-188-065.sfo1.dsl.speakeasy.net [66.92.188.65]) by svr1.postgresql.org (Postfix) with ESMTP id 2252E53429; Thu, 24 Mar 2005 17:01:39 +0000 (GMT) Received: from fetter.org (localhost.localdomain [127.0.0.1]) by fetter.org (8.13.1/8.12.10) with ESMTP id j2OH1bx4022583; Thu, 24 Mar 2005 09:01:37 -0800 Received: (from shackle@localhost) by fetter.org (8.13.1/8.13.1/Submit) id j2OH1bYl022582; Thu, 24 Mar 2005 09:01:37 -0800 Date: Thu, 24 Mar 2005 09:01:37 -0800 From: David Fetter To: PostgreSQL Docs , PostgreSQL Patches Subject: Approximate count(*) Message-ID: <20050324170137.GA15848@fetter.org> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="UugvWAfsgieZRqgk" Content-Disposition: inline User-Agent: Mutt/1.4.2i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.056 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/14 X-Sequence-Number: 2910 --UugvWAfsgieZRqgk Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Folks, Please find enclosed a patch that shows how to get a quick approximation of count(*) on a table. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! --UugvWAfsgieZRqgk Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="approx_count.diff" Index: doc/src/sgml/func.sgml =================================================================== RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.241 diff -c -r1.241 func.sgml *** doc/src/sgml/func.sgml 14 Mar 2005 18:31:19 -0000 1.241 --- doc/src/sgml/func.sgml 24 Mar 2005 16:25:55 -0000 *************** *** 7330,7339 **** ! Unfortunately, there is no similarly trivial query that can be ! used to improve the performance of count() ! when applied to the entire table. --- 7330,7348 ---- ! When the table has been VACUUMed recently, but ! only then, a good approximation of count(*) for an entire table ! can be obtained as follows: ! ! SELECT reltuples FROM pg_class WHERE relname = 'sometable'; ! ! ! ! ! Unfortunately, there is not yet a general trivial query that can ! be used to improve the performance of count(). + --UugvWAfsgieZRqgk--