Re: [BUGS] BUG #6325: Useless Index updates

Continuing this talk on general, as requested by Craig.

I have a functional Index on a table that is relative expensive to calculate. Now I noticed on every update of even index-unrelated fields of the table the index function is calculated again and again.

I currenly understand that if the update moves the row to a new location (no HOT replacement), the key to the index has to be calculated from the old and the new row to update the index.

This is expensive in my case, and useless, if the input to the immutable index function has not changed in my update statement, and as such the calculation should always be done just once. In case of HOT replacement, it hasn't to be done at all.

I assume, that comparing values to each other is in most times cheaper than calling a function have these values as parameters. If there is a high cost on the function (>1000?), it would be a good thing to always check if the inputs to the function have changed, before calling this function once or twice. Since I have a lot of functional indexes, I would greatly profit from an improvement in this area.

Is anyone interested in implementing this?

Regards,
Daniel Migowski
________________________________ ________
Von: Craig Ringer [ringerc(at)ringerc(dot)id(dot)au]
Gesendet: Sonntag, 4. Dezember 2011 15:02
Bis: Daniel Migowski
Cc: pgsql-bugs(at)postgresql(dot)org
Betreff: Re: [BUGS] BUG #6325: Useless Index updates

On 12/04/2011 08:54 PM, dmigowski(at)ikoffice(dot)de wrote:
> 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?

- 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