To: Josh Berkus <josh@agliodbs.com>
cc: pgsql-performance@postgresql.org
Subject: Re: FWD: Update touches unrelated indexes? 
In-reply-to: <44A4C5B0.2090303@agliodbs.com> 
References: <44A4C5B0.2090303@agliodbs.com>
Comments: In-reply-to Josh Berkus <josh@agliodbs.com>
	message dated "Thu, 29 Jun 2006 23:33:20 -0700"
Date: Fri, 30 Jun 2006 02:43:54 -0400
Message-ID: <20925.1151649834@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Josh Berkus <josh@agliodbs.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