On Thursday, February 1, 2018, Konstantin Knizhnik <
k.knizhnik@postgrespro.ru> wrote:
Old + New for check = 2
plus calculate again in index = 3
Yes, we have to calculate the value of index expression for original and updated version of the record. If them are equal, then it is all we have to do with this index: hot update is applicable.
In this case we calculate index expression twice.
But if them are not equal, then hot update is not applicable and we have to update index. In this case expression will be calculated one more time. So totally three times.
This is why, if calculation of index expression is very expensive, then effect of this optimization may be negative even if value of index expression is not changed.
For the old/new comparison and the re-calculate if changed dynamics - is this a side effect of separation of concerns only or is there some other reason the old computed value already stored in the index isn't compared to the one and only function result of the new tuple which, if found to be different, is then stored. One function invocation, which has to happen anyway, and one extra equality check. Seems like this could be used for non-functional indexes too, so that mere presence in the update listing doesn't negate HOT if the column didn't actually change (if I'm not mis-remembering something here anyway...)
Also, create index page doc typo from site: "with an total" s/b "with a total" (expression cost less than 1000) - maybe add a comma for 1,000
David J.