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

[Zeev Ben-Sender <zeevb@xxxxxxxxxxxxxx>]

Thanks for the reply. I tested the issue and found out that the row has moved to another page, which means that the index was rebuilt.

Now the question is about the cost of such rebuild: is it the same as update with different value or update with the same value costs less.

 

---

From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Vick Khera
Sent: Tuesday, April 05, 2011 8:46 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: 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@xxxxxxxxxxxxxx> 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.

 

Scanned by Check Point Total Security Gateway.