From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: FWD: Update touches unrelated indexes?
Date: Fri, 30 Jun 2006 02:43:54 -0400
Message-ID: <20925.1151649834@sss.pgh.pa.us> (raw)
In-Reply-To: <44A4C5B0.2090303@agliodbs.com>
References: <44A4C5B0.2090303@agliodbs.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, josh@agliodbs.com
Subject: Re: FWD: Update touches unrelated indexes?
In-Reply-To: <20925.1151649834@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox