Обсуждение: are there any method that "Update" command not affect other unrelated indices?

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?
thanks!

Well, the objects indices 1,2,3 point to changed when you changed column a4, but I don't know if that's the reason. I would guess that the indices are structured as pointers of some kind though.

Thanks. I could give more clues.
The call stack of the function most consumed time is:
Thread [1] (Suspended)   
    34 ExecInsertIndexTuples() /home/postgres/develop/postgresql-snapshot/src/backend/executor/execUtils.c:1046 0x08201e66   
    33 ExecUpdate() /home/postgres/develop/postgresql-snapshot/src/backend/executor/execMain.c:2135 0x081f3b13   
    32 ExecutePlan() /home/postgres/develop/postgresql-snapshot/src/backend/executor/execMain.c:1681 0x081f31c6   
    31 standard_ExecutorRun() /home/postgres/develop/postgresql-snapshot/src/backend/executor/execMain.c:309 0x081f0f4b   
    30 ExecutorRun() /home/postgres/develop/postgresql-snapshot/src/backend/executor/execMain.c:258 0x081f0e04   
    29 _SPI_pquery() /home/postgres/develop/postgresql-snapshot/src/backend/executor/spi.c:2009 0x0821fe8c   
    28 _SPI_execute_plan() /home/postgres/develop/postgresql-snapshot/src/backend/executor/spi.c:1831 0x0821facd   
    27 SPI_execute_plan() /home/postgres/develop/postgresql-snapshot/src/backend/executor/spi.c:392 0x0821d201   

in execMain.c, the call of ExecInsertIndexTuples() is as following:
if (resultRelInfo->ri_NumIndices > 0 && !HeapTupleIsHeapOnly(tuple))
        ExecInsertIndexTuples(slot, &(tuple->t_self), estate, false);

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!

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);


Is there any reason you're using gist indices on integer fields? Those are primarily used with full-text searching, I
wouldn'texpect them to be particularly efficient for scalar values. A (default) btree would probably perform better. 

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4cb55736678302085914008!

> -----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

On Wed, Oct 13, 2010 at 10:31 AM, Igor Neyman <ineyman@perceptron.com> wrote:
> 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.
>

If you qualify for a HOT update the indexes do not need updating.