On 25.05.2017 19:37, Tom Lane wrote:

> Konstantin Knizhnik <

[hidden email]> 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.comThe Russian Postgres Company

--

Sent via pgsql-hackers mailing list (

[hidden email])

To make changes to your subscription:

http://www.postgresql.org/mailpref/pgsql-hackers