Re: [HACKERS] Surjective functional indexes

On Fri, May 11, 2018 at 4:58 AM, Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> wrote:

>
> Sorry, may be I do not completely understand you.
> So whats happed before this patch:
>
> - On update postgres compares old and new values of all changed attributes
> to determine whether them are actually changed.
> - If value of some indexed attribute is changed, then hot update is not
> applicable and we have to rebuild indexed.
> - Otherwise hot update is used and indexes should not be updated.
>
> What is changed:
>
> - When some of attributes, on which functional index depends, is changed,
> then we calculate value of index expression. It is done using existed
> FormIndexDatum function which stores calculated expression value in the
> provided slot. This evaluation of index expressions and their comparison is
> done in access/heap/heapam.c file.
> - Only if old and new values of index expression are different, then hot
> update is really not applicable.
>
>
​Thanks.

So, in my version of layman's terms, before this patch we treated simple
column referenced indexes and expression indexes differently because in a
functional index we didn't actually compare the expression results, only
the original values of the depended-on columns. With this patch both are
treated the same - and in a manner that I would think would be
normal/expected. Treating expression indexes this way, however, is
potentially more costly than before and thus we want to provide the user a
way to say "hey, PG, don't bother with the extra effort of comparing the
entire expression, it ain't gonna matter since if I change the depended-on
values odds are the expression result will change as well." Changing an
option named "recheck_on_update" doesn't really communicate that to me (I
dislike the word recheck, too implementation specific). Something like
"only_compare_dependencies_on_update (default false)" would do a better job
at that - tell me what the abnormal behavior is, not the normal, and lets
me enable it instead of disabling the default behavior and not know what it
is falling back to. The description for the parameter does a good job of
describing this - just suggesting that the name match how the user might
see things.

On the whole the change makes sense - and the general assumptions around
runtime cost seem sound and support the trade-off simply re-computing the
expression on the old tuple versus engineering an inexpensive way to
retrieve the existing value from the index.

I don't have a problem with "total expression cost" being used as a
heuristic - though maybe that would mean we should make this an enum with
(off, on, never/always) with the third option overriding all heuristics.

David J.
​