Re: Exact index overhead

Pavan Deolasee wrote:
> On Thu, Apr 17, 2008 at 2:57 PM, Gunther Mayer
> <gunther(dot)mayer(at)googlemail(dot)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