Re: Surjective functional indexes

On 13.09.2017 14:00, Simon Riggs wrote:
> On 13 September 2017 at 11:30, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>
>> The only reason of all this discussion about terms is that I need to choose
>> name for correspondent index option.
>> Simon think that we do not need this option at all. In this case we should
>> not worry about right term.
>> From my point of view, "projection" is quite clear notion and not only for
>> mathematics. It is also widely used in IT and especially in DBMSes.
> If we do have an option it won't be using fancy mathematical
> terminology at all, it would be described in terms of its function,
> e.g. recheck_on_update
>
> Yes, I'd rather not have an option at all, just some simple code with
> useful effect, like we have in many other places.
>
Yehhh,
After more thinking I found out that my idea to use table/index
statistic (particularity number of distinct values) to determine
projection functions was wrong.
Consider case column bookinfo of jsonb type and index expression
(bookinfo->'ISBN').
Both can be considered as unique. But it is an obvious example of
projection function, which value is not changed if we update other
information related with this book.

So this approach doesn't work. Looks like the only thing we can do to
autotune is to collect own statistic: how frequently changing
attribute(s) doesn't affect result of the function.
By default we can considered function as projection and perform
comparison of old/new function results.
If after some number of comparisons fraction of hits (when value of
function is not changed) is smaller than some threshold (0.5?, 0.9?,...)
then we can mark index as non-projective
and eliminate this checks in future. But it will require extending index
statistic. Do we really need/want it?

Despite to the possibility to implement autotune, I still think that we
should have manual switch, doesn't mater how it is named.

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