Re: are there any method that "Update" command not affect other unrelated indices? - Mailing list pgsql-general

On 13 Oct 2010, at 24:03, sunpeng wrote:

> Hi, I have the following table:
> CREATE TABLE A
> (
>    a1 integer not null,
>    a2 integer,
>    a3 integer,
>    a4 integer
> )
> and have the following four indices:
> create index ind_a1 on A USING gist(a1);
> create index ind_a2 on A USING gist(a2);
> create index ind_a3 on A USING gist(a3);
> create index ind_a4 on A USING gist(a4);
>
> now we have 10,000 update command executions using spi_exeplan():
> SPI_prepare(); // prepare the plan for "update A set a4 = $1;"
> for(i=0;i<10000;i++ ){
>    SPI_execute_plan();//   update A set a4 = i;
> }
>
> the question is why all four indices updated in the execution of SPI_execute_plan()?
> I think there should only one index, that is ind_a4 be updated, how to avoid other three indices updated?

Obviously the indices need to point to the new version of the row as well, or you won't be able to find the new version
usingthe other indices. 

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4cb55404678308231573016!