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

On 12/04/2011 08:54 PM, dmigowski@ikoffice.de wrote:
> 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:
>
> 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?

- This isn't a bug report, it's a feature/enhancement request. Please
   use the mailing lists.

- You're reporting this issue against an old patch release of an old
   major release. Why not check with 9.1?

- The index isn't always updated. Check out HOT (introduced in 8.4, the
   release after your current one) which reduces unnecessary index
   updates in cases where the old and new row can fit on the same
   heap page.

- In most other cases the index update can't be avoided, because
   the new and old rows are on different database pages. The old index
   entry has to remain in place so that still-running transactions that
   can see the old row can still find it in the index, so it can't be
   overwritten and instead a new entry has to be added.

> 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.

That's a more interesting one. Perhaps you could write it up in more
detail, with a test case, and submit it to the pgsql-general mailing list?

This isn't just about functions anyway. Pg would have to compare *all*
inputs to the old index expression to see if they were the same.
Otherwise, in an expression like  f(g(x,y),z)  Pg would not have any
stored value for the result of g(x,y) to compare against. It'd have to
instead compare (x1,y1,z1) to (x2,y2,z2) and decide that if they were
the same the result of the index expression hadn't changed.

That's probably possible, but I'm not sure it'd be a win over just
evaluating the expression in most cases. How would Pg know when to do
it? Using function COST parameters?

Essentially, this isn't as simple as it looks at face value.

> 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?)

No, it isn't possible. Index functions must be immutable, not just
stable, so their output must be determined entirely by their parameters.
At least on newer versions STABLE or VOLATILE functions should be
rejected in index expressions.

--
Craig Ringer