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 4AEDAD1B4EF for ; Fri, 3 Oct 2003 02:20:13 +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 29474-08 for ; Thu, 2 Oct 2003 23:19:31 -0300 (ADT) Received: from news.hub.org (unknown [64.117.224.194]) by svr1.postgresql.org (Postfix) with ESMTP id 54ED6D1B508 for ; Thu, 2 Oct 2003 23:19:27 -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 h932JQQj061698 for ; Fri, 3 Oct 2003 02:19:27 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h932CGcj060938 for pgsql-performance@postgresql.org; Fri, 3 Oct 2003 02:12:16 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: count(*) slow on large tables Date: Thu, 02 Oct 2003 22:08:18 -0400 Organization: cbbrowne Computing Inc Lines: 43 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> 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:nqN2S1g4IaIUHFMhdc9gGlxONnA= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/73 X-Sequence-Number: 3821 The world rejoiced as dror@zapatec.com (Dror Matalon) wrote: > I don't have an opinion on how hard it would be to implement the > tracking in the indexes, but "select count(*) from some table" is, in my > experience, a query that people tend to run quite often. > One of the databases that I've used, I believe it was Informix, had that > info cached so that it always new how many rows there were in any > table. It was quite useful. I can't imagine why the raw number of tuples in a relation would be expected to necessarily be terribly useful. I'm involved with managing Internet domains, and it's only when people are being pretty clueless that anyone imagines that "select count(*) from domains;" would be of any use to anyone. There are enough "test domains" and "inactive domains" and other such things that the raw number of "things in the table" aren't really of much use. - I _do_ care how many pages a table occupies, to some extent, as that determines whether it will fit in my disk space or not, but that's not COUNT(*). - I might care about auditing the exact numbers of records in order to be assured that a data conversion process was done correctly. But in that case, I want to do something a whole *lot* more detailed than mere COUNT(*). I'm playing "devil's advocate" here, to some extent. But realistically, there is good reason to be skeptical of the merits of using SELECT COUNT(*) FROM TABLE for much of anything. Furthermore, the relation that you query mightn't be a physical "table." It might be a more virtual VIEW, and if that's the case, bets are even MORE off. If you go with the common dictum of "good design" that users don't directly access tables, but go through VIEWs, users may have no way to get at SELECT COUNT(*) FROM TABLE. -- output = reverse("ac.notelrac.teneerf" "@" "454aa") http://www.ntlug.org/~cbbrowne/finances.html Rules of the Evil Overlord #74. "When I create a multimedia presentation of my plan designed so that my five-year-old advisor can easily understand the details, I will not label the disk "Project Overlord" and leave it lying on top of my desk."