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

Reply via email to