Re: [HACKERS] Surjective functional indexes

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Stephen Frost <sfrost(at)snowman(dot)net>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Christoph Berg <myon(at)debian(dot)org>, Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] Surjective functional indexes
Date: 2018-02-01 08:49:24
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 01.02.2018 03:10, Simon Riggs wrote:
> On 10 January 2018 at 09:54, Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>> (new version attached)
> Why this comment?
> Current implementation of projection optimization has to calculate
> index expression twice
> in case of hit (value of index expression is not changed) and three
> times if values are different.
> Old + New for check = 2
> plus calculate again in index = 3
> ?
Sorry, I do not completely understand your question.
You do not agree with this statement or you think that this comment is
irrelevant in this place?
Or you just want to understand why index expression is calculated 2/3
times? If so, then you have answered this question yourself:
> Old + New for check = 2
> plus calculate again in index = 3

Yes, we have to calculate the value of index expression for original and
updated version of the record. If them are equal, then it is all we have
to do with this index: hot update is applicable.
In this case we calculate index expression twice.
But if them are not equal, then hot update is not applicable and we have
to update index. In this case expression will be calculated one more
time. So totally three times.
This is why, if calculation of index expression is very expensive, then
effect of this optimization may be negative even if value of index
expression is not changed.

Konstantin Knizhnik
Postgres Professional:
The Russian Postgres Company

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2018-02-01 08:53:31 Re: constraint exclusion and nulls in IN (..) clause
Previous Message Tomas Vondra 2018-02-01 08:45:50 Re: STATISTICS retained in CREATE TABLE ... LIKE (INCLUDING ALL)?