X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 97D6BD1CCDA for ; Sat, 1 May 2004 09:48:48 -0300 (ADT) 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 79966-06 for ; Sat, 1 May 2004 09:48:30 -0300 (ADT) Received: from gpdnet.co.uk (gpdnet.plus.com [212.56.100.243]) by svr1.postgresql.org (Postfix) with ESMTP id B7944D1EAEE for ; Sat, 1 May 2004 09:48:25 -0300 (ADT) Received: from gary (unverified [192.168.1.2]) by gpdnet.co.uk (SurgeMail 1.8g3) with ESMTP id 143 for ; Sat, 01 May 2004 13:47:41 +0100 From: "Gary Doades" To: pgsql-performance@postgresql.org Date: Sat, 01 May 2004 13:48:35 +0100 MIME-Version: 1.0 Subject: Re: planner/optimizer question Message-ID: <4093AAB3.21370.742972@localhost> In-reply-to: <4093876C.2060409@oli.tudelft.nl> References: <8881.1083374379@sss.pgh.pa.us> X-mailer: Pegasus Mail for Windows (v4.12a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Server: High Performance Mail Server - http://surgemail.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/4 X-Sequence-Number: 6835 On 1 May 2004 at 13:18, Jochem van Dieten wrote: > Yes, really :-) > > When a tuple is inserted the visible-to-all flag is set to false. > The effect of this is that every index scan that finds this tuple > has to visit the heap to verify visibility. If it turns out the > tuple is not only visible to the current transaction, but to all > current transactions, the visible-to-all flag can be set to true. > This is non-critical, because if it is set to false scans will > not miss the tuple, they will just visit the heap to verify > visibility. > > The moment the heap tuple is updated/deleted the visible-to-all > flag needs to be set to false again in all indexes. This is > critical, and the I/O and (dead)lock costs of unsetting the > visible-to-all flag are unknown and might be big enough to ofset > any advantage on the selects. > > But I believe that for applications with a "load, select, drop" > usage pattern (warehouses, archives etc.) having this > visible-to-all flag would be a clear winner. > > Jochem > If needs be this index maintenance could be set as a configuration option. It is likely that database usage patterns are reasonably well known for a particular installation. This option could be set on or off dependant on typical transactions. In my case with frequent large/complex selects and few very short insert/updates I think it could be a big performance boost. If it works :-) Regards, Gary.