DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=ioP4RzZlwNKlu+dPYDAAnmLcOWPR5k/JsoOwjD2DuH/T9PrBG1RHVZdsbuIp2ylHU2DuJDvXS/LJntfCtfVRx5YJzkA7A0aFqYBgFTsUguiVJ+1jH+yFTIseAQfMpURVhrEz4gPTK4LCokaLPCatZR4pRtvad6Kv3Lub2VqhUVU=
Message-ID: <48076FCD.2090100@googlemail.com>
Date: Thu, 17 Apr 2008 17:42:05 +0200
From: Gunther Mayer <gunther.mayer@googlemail.com>
User-Agent: Thunderbird 2.0.0.12 (X11/20080227)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: Exact index overhead
References: <48071807.8000409@gmail.com>
	<2e78013d0804170316v2b62d7afy87c3753fb0b55842@mail.gmail.com>
In-Reply-To: <2e78013d0804170316v2b62d7afy87c3753fb0b55842@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Pavan Deolasee wrote:
> On Thu, Apr 17, 2008 at 2:57 PM, Gunther Mayer
> <gunther.mayer@googlemail.com> wrote:
>   
>>  You see, all updates change most of the data fields but never ever touch
>> the time field. Assuming correct and efficient behaviour of postgresql it
>> should then also never touch the time index and incur zero overhead in its
>> presence, but is this really the case?
>>     
>
> Normally, whenever a row is updated, Postgres inserts a new index entry in each
> of the index. So to answer your question, there is certainly index
> overhead during
> updates, even if you are not changing the indexed column.
>   
Ah, I knew these "obvious" assumptions wouldn't necessarily hold. Good 
that I checked.
> But if you are using 8.3 then HOT may help you here, assuming you are
> not updating
> any index keys. HOT optimizes the case by *not* inserting a new index entry and
> also by performing retail vacuuming. The two necessary conditions for HOT are:
>
> 1. Update should not change any of the index keys. So if you have two
> indexes, one
> on column A and other on column B, update must not be modifying either A or B.
>   
That condition is always satisfied.
> 2. The existing block should have enough free space to accommodate the
> new version
> A less than 100 fillfactor may help you given your rate of updates.
>   
I see, as soon as a new block is required for the new version the index 
pointer needs updating too, I understand now. But at least in the common 
case of space being available the index overhead is reduced to zero. I 
can live with that.
> If your application satisfies 1, then I would suggest you to upgrade
> to 8.3 (if you are
> not using it already) and then you can create the index without
> bothering much about
> overheads.
>   
I'm still running 8.2.7 but I guess here's a compelling reason to 
upgrade ;-) Will do so soon.

Thanks a lot to everyone who responded (and at what pace!). I love this 
community, it beats commercial support hands down.

Gunther