Re: Exact index overhead

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.

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.

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.

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.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com