Postgres Pro
Downloads
Home   >   mailing lists

Re: Is index rebuilt upon updating table with the same values as already existing in the table? - Mailing list pgsql-general

From Zeev Ben-Sender
Subject Re: Is index rebuilt upon updating table with the same values as already existing in the table?
Date
Msg-id 217DDBC2BB1E394CA9E7446337CBDEF2A8889B1ACE@il-ex01.ad.checkpoint.com
Whole thread Raw
In response to Re: Is index rebuilt upon updating table with the same values as already existing in the table?  (Vick Khera <vivek@khera.org>)
List pgsql-general
Tree view

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

 



Scanned by Check Point Total Security Gateway.

pgsql-general by date:

Previous
From: Vibhor Kumar
Date:
Subject: Re: Dumping functions with pg_dump
Next
From: Simon Riggs
Date:
Subject: Re: Why security-definer functions are executable by public by default?