Re: 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.

On Wed, Oct 13, 2010 at 9:03 AM, sunpeng <bluevaley(at)gmail(dot)com> wrote:

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

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

2010/10/12 Ben Carbery <ben(dot)carbery(at)gmail(dot)com>

> 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.
>
>
> On Wed, Oct 13, 2010 at 9:03 AM, sunpeng <bluevaley(at)gmail(dot)com> wrote:
>
>>
>> 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!
>>
>>

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!

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't expect 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(at)solfertje(dot)student(dot)utwente(dot)nl]
> Sent: Wednesday, October 13, 2010 2:38 AM
> To: sunpeng
> Cc: pgsql-general(at)postgresql(dot)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(at)perceptron(dot)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.