Re: FWD: Update touches unrelated indexes?

Hi Tom,

>This surprises you why?

I don't know anything about how PG stores keys along with their
references to the actual rows but my assumption was that that reference
is some sort of an index into a table that maps the reference to an
actual disk/file address. So even if the row or the page with the row on
it is physically moved to a different location in the disk file, the
unrelated indexes would not have to be changed because only the
disk/file address changes but the reference does not. If PG does not
work in a similar fashion then I understand the locks.

> That last I don't believe at all --- PG updates every index on every
row
>update. Most likely the OP is just not querying pg_locks fast enough
to
>see the locks.

I'm sure you are right, but I was doing the update in a transaction and
I did not see those looks after the update was done but before the
changes were committed.

>he probably needs to think harder about whether every one of those
>indexes is really carrying its weight.

Unfortunately all of those indexes are required by the application. It
appears that the only viable option I have is to drop the indexes and
recreate them after the update.

Thanks for the help!
Jozsef

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
Sent: Friday, June 30, 2006 1:44 AM
To: Josh Berkus
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] FWD: Update touches unrelated indexes?

Josh Berkus <josh(at)agliodbs(dot)com> forwards:
> I hope someone can explain what I'm seeing on our system. I've got a
> table with about four million rows in it (see schema below). Almost
> every column has one or two indexes. What I've found is that when I
> issue an update statement to zero out the content of a particular
> column, the pg_locks table indicates that every other, seemingly
> unrelated index is locked/changed.

This surprises you why?

> I expect the idx_test_table_col_27 index to have write locks during
this
> operation but seeing RowExclusiveLock entries on every other index
> puzzles me. Interestingly enough these locks are not present if the
> table is smaller.

That last I don't believe at all --- PG updates every index on every row
update. Most likely the OP is just not querying pg_locks fast enough to
see the locks. If he's really concerned about update performance then
he probably needs to think harder about whether every one of those
indexes is really carrying its weight.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster