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 F2099D1B4F0 for ; Fri, 3 Oct 2003 22:20:26 +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 54057-05 for ; Fri, 3 Oct 2003 19:19:42 -0300 (ADT) Received: from news.hub.org (unknown [64.117.224.194]) by svr1.postgresql.org (Postfix) with ESMTP id 33F72D1B51A for ; Fri, 3 Oct 2003 19:19:40 -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 h93MJdQp054653 for ; Fri, 3 Oct 2003 22:19:40 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id h93M4NEG052965 for pgsql-performance@postgresql.org; Fri, 3 Oct 2003 22:04:23 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: count(*) slow on large tables Date: Fri, 03 Oct 2003 17:55:25 -0400 Organization: cbbrowne Computing Inc Lines: 31 Message-ID: References: <20031002191547.GZ87525@rlx11.zapatec.com> <20031002193905.GD18417@wolff.to> <3F7C98B8.C892D0E5@nsd.ca> <60brszcng5.fsf@dev6.int.libertyrms.info> <3F7D9A57.B37A0DFC@nsd.ca> 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:2KvR0uAbh1RBj2RGL/9Y0ZwIfBE= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/118 X-Sequence-Number: 3866 In the last exciting episode, jllachan@nsd.ca (Jean-Luc Lachance) wrote: > Well I can think of many more case where it would be usefull: > > SELECT COUNT(DISTINCT x) FROM ... > SELECT COUNT(*) FROM ... WHERE x = ? Those are precisely the cases that the "other databases" ALSO fall down on. Maintaining those sorts of statistics would lead [in _ANY_ database; PostgreSQL has no disadvantage in this] to needing for each and every update to update a whole host of statistic values. It would be fairly reasonable to have a trigger, in PostgreSQL, to manage this sort of information. It would not be outrageously difficult to substantially improve performance of queries, at the considerable cost that each and every update would have to update a statistics table. If you're doing a whole lot of these sorts of queries, then it is a reasonable idea to create appropriate triggers for the (probably very few) tables where you are doing these counts. But the notion that this should automatically be applied to all tables always is a dangerous one. It would make update performance Suck Badly, because the extra statistical updates would be quite expensive. -- wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','cbbrowne.com'). http://www3.sympatico.ca/cbbrowne/multiplexor.html I'm sorry Dave, I can't let you do that. Why don't you lie down and take a stress pill?