BUG #6325: Useless Index updates - Mailing list pgsql-bugs

From dmigowski@ikoffice.de
Subject BUG #6325: Useless Index updates
Date
Msg-id E1RXBaL-0000K2-U2@wrigleys.postgresql.org
Whole thread Raw
Responses Re: BUG #6325: Useless Index updates
Re: BUG #6325: Useless Index updates
List pgsql-bugs
The following bug has been logged on the website:

Bug reference:      6325
Logged by:          Daniel Migowski
Email address:      dmigowski@ikoffice.de
PostgreSQL version: 8.3.16
Operating system:   Linux
Description:=20=20=20=20=20=20=20=20

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?=20

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.

pgsql-bugs by date:

Previous
From: MirrorX
Date:
Subject: could not truncate directory "pg_subtrans": apparent wraparound
Next
From: Craig Ringer
Date:
Subject: Re: BUG #6325: Useless Index updates