On Thu, Apr 17, 2008 at 2:57 PM, Gunther Mayer <[EMAIL PROTECTED]> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
