On Thu, Jun 12, 2014 at 10:22 AM, Torsten Förtsch <torsten.foertsch@xxxxxxx> 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
