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.
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