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@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] On Behalf Of Tom Lane Sent: Friday, June 30, 2006 1:44 AM To: Josh Berkus Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: [PERFORM] FWD: Update touches unrelated indexes? Josh Berkus <josh@xxxxxxxxxxxx> 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
