X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 12236D1B436 for ; Tue, 6 Jan 2004 02:05:17 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 15271-07 for ; Mon, 5 Jan 2004 22:04:29 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 15386D1B4AF for ; Mon, 5 Jan 2004 22:04:27 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i0624QU6061594 for ; Tue, 6 Jan 2004 02:04:26 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i061rUi0054163 for pgsql-performance@postgresql.org; Tue, 6 Jan 2004 01:53:30 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Select max(foo) and select count(*) optimization Date: Mon, 05 Jan 2004 20:46:00 -0500 Organization: cbbrowne Computing Inc Lines: 29 Message-ID: References: <1073348666.19994.3.camel@localhost.localdomain> 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:CGrCaeE+coVOy09ohrN8ktqXNRI= To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/59 X-Sequence-Number: 5234 Martha Stewart called it a Good Thing when paul@tuckfield.com (Paul Tuckfield) wrote: > Not that I'm offering to do the porgramming mind you, :) but . . > > In the case of select count(*), one optimization is to do a scan of the > primary key, not the table itself, if the table has a primary key. In a > certain commercial, lesser database, this is called an "index fast full > scan". It would be important to scan the index in physical order > (sequential physical IO) and not in key order (random physical IO) The problem is that this "optimization" does not actually work. The index does not contain transaction visibility information, so you have to go to the pages of tuples in order to determine if any given tuple is visible. > I'm guessing the payoff as well as real-world-utility of a max(xxx) > optimization are much higher than a count(*) optimization tho That's probably so. In many cases, approximations, such as page counts, may be good enough, and pray consider, that ("an approximation") is probably all you were getting from the database systems that had an "optimization" to store the count in a counter. -- let name="cbbrowne" and tld="ntlug.org" in name ^ "@" ^ tld;; http://www3.sympatico.ca/cbbrowne/linuxxian.html "No, you misunderstand. Microsoft asked some hackers how they could make their system secure - the hackers replied "Turn it off.". So they did." -- Anthony Ord