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. ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html