> -----Original Message-----
> From: Alban Hertroys [mailto:dalroi@solfertje.student.utwente.nl]
> Sent: Wednesday, October 13, 2010 2:38 AM
> To: sunpeng
> Cc: pgsql-general@postgresql.org
> Subject: Re: are there any method that "Update" command not
> affect other unrelated indices?
>
> 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
> using the other indices.
>
> Alban Hertroys
>
> --
> Screwing up is an excellent way to attach something to the ceiling.
>
>
> !DSPAM:737,4cb55404678308231573016!
>
Just to make clear what Alban said, because it looks like OP is not
familiar with Postgres MVCC model.
All indices need to be updated, because Postgres does not do "upgrade in
place", like some other databases do.
When any column is updated, new version of the row created and the old
one marked as deleted.
Regards,
Igor Neyman