X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 117B0D1B51A for ; Fri, 3 Oct 2003 11:50:19 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 22031-07 for ; Fri, 3 Oct 2003 08:49:36 -0300 (ADT) Received: from news.hub.org (unknown [64.117.224.194]) by svr1.postgresql.org (Postfix) with ESMTP id 442D7D1B50D for ; Fri, 3 Oct 2003 08:49:33 -0300 (ADT) Received: from news.hub.org (host-64-117-224-194.altec1.com [64.117.224.194] (may be forged)) by news.hub.org (8.12.9/8.12.9) with ESMTP id h93BnXQj083484 for ; Fri, 3 Oct 2003 11:49:33 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h93BiYkl082815 for pgsql-performance@postgresql.org; Fri, 3 Oct 2003 11:44:34 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: count(*) slow on large tables Date: Fri, 03 Oct 2003 07:37:07 -0400 Organization: cbbrowne Computing Inc Lines: 38 Message-ID: References: <20031002191547.GZ87525@rlx11.zapatec.com> <20031002193905.GD18417@wolff.to> <3F7C98B8.C892D0E5@nsd.ca> <60brszcng5.fsf@dev6.int.libertyrms.info> <20031002223313.GE87525@rlx11.zapatec.com> <20031003042754.GH87525@rlx11.zapatec.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1003 (Gnus v5.10.3) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:FyuTA7XIMR7BGzzd/bnO8Qr0n2A= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/83 X-Sequence-Number: 3831 Oops! dror@zapatec.com (Dror Matalon) was seen spray-painting on a wall: > I smell a religious war in the aii:-). > Can you go several days in a row without doing select count(*) on any > of your tables? I would be more likely, personally, to run "VACUUM VERBOSE ANALYZE", which has useful side-effects :-). > I suspect that this is somewhat a domain specific issue. In some > areas you don't need to know the total number of rows in your > tables, in others you do. "Relationship tables," which don't contain data in their own right, but which, instead, link together records in other tables, are likely to have particularly useless COUNT(*)'s. > I also suspect that you're right, that end user applications don't > use this information as often as DBAs would. On the other hand, it > seems whenever you want to optimize your app (something relevant to > this list), one of the things you do need to know is the number of > rows in your table. Ah, but in the case of optimization, there's little need for "transactionally safe, MVCC-managed, known-to-be-exact" values. Approximations are plenty good enough to get the right plan. Furthermore, it's not the number of rows that is most important when optimizing queries; the number of pages are more relevant to the matter, as that's what the database is slinging around. -- (reverse (concatenate 'string "ac.notelrac.teneerf" "@" "454aa")) http://www3.sympatico.ca/cbbrowne/multiplexor.html Rules of the Evil Overlord #134. "If I am escaping in a large truck and the hero is pursuing me in a small Italian sports car, I will not wait for the hero to pull up along side of me and try to force him off the road as he attempts to climb aboard. Instead I will slam on the brakes when he's directly behind me. (A rudimentary knowledge of physics can prove quite useful.)"