Re: updates not causing changes

On Thu, Jun 12, 2014 at 10:22 AM, Torsten Förtsch <torsten(dot)foertsch(at)gmx(dot)net>
wrote:

> Hi,
>
> our developers use a ORM tool which generates updates that write all
> columns of a table. In most cases, however, very few columns actually
> change. So, those updates mostly write the same value that already is
> there in the column.
>
> Now, if there is an index on such columns, does Postgres recognize the
> situation and skip the index update?

If any indexed column changes, than all indexes need to be updated, because
the tuple will get a new ctid and that is part of each index. If no
indexed columns change (and the new column fits in the same page), then it
can use HOT update and avoid the index maintenance.

For purposes of determining if an indexed column changes, it compares the
old value to the new value. It doesn't matter whether the column was
updated in a degenerate way, or if it was not in the update list in the
first place--either way the lack-of-change is detected.

Cheers,

Jeff