BUG #6325: Useless Index updates

From: dmigowski(at)ikoffice(dot)de
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #6325: Useless Index updates
Date: 2011-12-04 12:54:25
Message-ID: E1RXBaL-0000K2-U2@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-general

The following bug has been logged on the website:

Bug reference: 6325
Logged by: Daniel Migowski
Email address: dmigowski(at)ikoffice(dot)de
PostgreSQL version: 8.3.16
Operating system: Linux
Description:

It seems that an update to a row in a table always removes the element from
an index and adds it again. Wouldn't it be faster to check for equality of
the index parameters in the OLD and NEW record first?

I have this problem with an functional index using a relative expensive
index function, and noticed that the index function is always called even if
the parameter to the index function has not changed. Wouldn't it be better
to validate that the input to the index functions has not changed, instead
of calling the index function over and over again? Especially since the
index functions seems to be called with the new and the old value anyway.

I can understand that this might be a precaution in the case that the index
function isn't stable (is it even possible to use such a function for an
index?), but in the stable case comparing the input parameters allows for
much much faster table updates.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Craig Ringer 2011-12-04 14:02:57 Re: BUG #6325: Useless Index updates
Previous Message MirrorX 2011-12-04 12:27:51 could not truncate directory "pg_subtrans": apparent wraparound

Browse pgsql-general by date

  From Date Subject
Next Message Phoenix Kiula 2011-12-04 12:56:56 Re: Weird behavior: deleted row still in index?
Previous Message Szymon Guz 2011-12-04 11:55:05 Re: Weird behavior: deleted row still in index?