Re: Is index rebuilt upon updating table with the same values as already existing in the table?

On Tue, Apr 5, 2011 at 11:22 AM, Zeev Ben-Sender <zeevb(at)checkpoint(dot)com>wrote:

> Hi,
>
>
>
> Having the update statement like this:
>
> UPDATE my_table SET (COL1 = ‘05cf5219-38e6-46b6-a6ac-5bbc3887d16a’, COL2 =
> 28) WHERE COL3 = 35;
>
>
>
> Will this statement result indexes rebuild if COL1 and COL2 already equal
> ‘05cf5219-38e6-46b6-a6ac-5bbc3887d16a’ and 28?
>
>
>
> Thank you
>
>
>

Easy to test. If the row moved to another page, then yes, else no.

To see if the row moved, select the ctid and compare before and after:

select ctid from my_table where COL3=35;
UPDATE ...
select ctid from my_table where COL3=35;

The ctid is returned as a tuple indicating the page number and position
within that page.