pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gary Doades <gpd@gpdnet.co.uk>
To: pgsql-performance@postgresql.org
Subject: Re: planner/optimizer question
Date: Sat, 01 May 2004 13:48:35 +0100
Message-ID: <4093AAB3.21370.742972@localhost> (raw)
In-Reply-To: <4093876C.2060409@oli.tudelft.nl>
References: <8881.1083374379@sss.pgh.pa.us>

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.




view thread (37+ messages)  latest in thread

Message-ID: <4093AAB3.21370.742972@localhost>
Permalink:  ../4093AAB3.21370.742972@localhost/
Also on:    postgresql.org/message-id/4093AAB3.21370.742972@localhost

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: gpd@gpdnet.co.uk
  Subject: Re: planner/optimizer question
  In-Reply-To: <4093AAB3.21370.742972@localhost>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox