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 36BA7D1B472 for ; Tue, 6 Jan 2004 06:33:35 +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 07027-07 for ; Tue, 6 Jan 2004 02:32:51 -0400 (AST) Received: from smtp.pspl.co.in (unknown [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id D6888D1B52A for ; Tue, 6 Jan 2004 02:32:43 -0400 (AST) Received: (from root@localhost) by smtp.pspl.co.in (8.12.9/8.12.9) id i066WsdJ025621 for ; Tue, 6 Jan 2004 12:02:54 +0530 Received: from daithan.intranet.pspl.co.in (daithan.intranet.pspl.co.in [192.168.7.161]) (authenticated bits=0) by persistent.co.in (8.12.9/8.12.9) with ESMTP id i066WsJN025608 for ; Tue, 6 Jan 2004 12:02:54 +0530 From: Shridhar Daithankar To: pgsql-performance@postgresql.org Subject: Re: Select max(foo) and select count(*) optimization Date: Tue, 6 Jan 2004 12:01:53 +0530 User-Agent: KMail/1.5.4 References: <1073348666.19994.3.camel@localhost.localdomain> In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200401061201.53978.shridhar_daithankar@persistent.co.in> X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/62 X-Sequence-Number: 5237 On Tuesday 06 January 2004 07:16, Christopher Browne wrote: > 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. It was rejected as an idea to add transaction visibility information to indexes. The time I proposed, my idea was to vacuum tuples on page level while postgresql pushes buffers out of shared cache. If indexes had visibility information, they could be cleaned out of order than heap tuples. This wouldn't have eliminated vacuum entirely but at least frequently hit data would be clean. But it was rejected because of associated overhead. Just thought worh a mention.. Shridhar