On 25.05.2017 19:37, Tom Lane wrote:
Konstantin Knizhnik <k.knizh...@postgrespro.ru> writes:
My proposal is to check value of function for functional indexes instead
of just comparing set of effected attributes.
Obviously, for some complex functions it may  have negative effect on
update speed.
This is why I have added "surjective" option to index.
This seems overcomplicated.  We would have to compute the function
value at some point anyway.  Can't we refactor to do that earlier?

                        regards, tom lane

Check for affected indexes/applicability of HOT update and update of indexes themselves is done in two completely different parts of code. And if we find out that values of indexed expressions are not changed, then we can use HOT update and indexes should not be updated (so calculated value of function is not needed). And it is expected to be most frequent case.

Certainly, if value of indexed expression is changed, then we can avoid redundant calculation of function by storing result of calculations somewhere. But it will greatly complicate all logic of updating indexes. Please notice, that if we have several functional indexes and only one of them is actually changed, then in any case we can not use HOT and have to update all indexes. So we do not need to evaluate values of all indexed expressions. We just need to find first changed one. So we should somehow keep track values of which expression are calculated and which not.

One more argument. Originally Postgres evaluates index expression only once (when inserting new version of tuple to the index). Now (with this patch) Postgres has to evaluate expression three times in the worst case: calculate the value of expression for old and new tuples to make a decision bout hot update, and the evaluate it once again when performing index update itself. Even if I managed to store somewhere calculated value of the expression, we still have to perform twice more evaluations than before. This is why for expensive functions or for functions defined for frequently updated attributes (in case of JSON) such policy should be disabled. And for non-expensive functions extra overhead is negligible. Also there is completely no overhead if indexed expression is not actually changed. And it is expected to be most frequent case.

At least at the particular example with YCSB benchmark, our first try was just to disable index update by commenting correspondent check of updated fields mask. Obviously there are no extra function calculations in this case. Then I have implemented this patch. And performance is almost the same. This is why I think that simplicity and modularity of code is more important here than elimination of redundant function calculation.

Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to