Re: Surjective functional indexes

On 13.09.2017 10:51, Christoph Berg wrote:
> Re: Konstantin Knizhnik 2017-09-01 <f530ede0-1bf6-879c-c362-34325514f692(at)postgrespro(dot)ru>
>> + Functional index is based on on projection function: function which extract subset of its argument.
>> + In mathematic such functions are called non-injective. For injective function if any attribute used in the indexed
>> + expression is changed, then value of index expression is also changed.
> This is Just Wrong. I still think what you are doing here doesn't have
> anything to do with the function being injective or not.

Sorry, can you please explain what is wrong?
The problem I am trying to solve comes from particular use case:
functional index on part of JSON column.
Usually such index is built for persistent attributes, which are rarely
changed, like ISBN...
Right now any update of JSON column disables hot update. Even if such
update doesn't really affect index.
So instead of disabling HOT juts based on mask of modified attributes, I
suggest to compare old and new value of index expression.

Such behavior can significantly (several times) increase performance.
But only for "projection" functions.
There was long discussion in this thread about right notion for this
function (subjective, non-injective, projection).
But I think criteria is quite obvious.

Simon propose eliminate "projection" property and use autotune to
determine optimal behavior.
I still think that such option will be useful, but we can really use
statistic to compare number of unique values for index function and for
it's argument(s).
If them are similar, then most likely the function is injective, so it
produce different result for different attributes.
Then there is no sense to spend extra CPU time, calculating old and new
values of the function.
This is what I am going to implement now.

So I will be please if you more precisely explain your concerns and
suggestions (if you have one).

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