Pavan Deolasee wrote:
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.
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
