Thread: FWD: Update touches unrelated indexes?
Folks,
Jozsef is having trouble posting to the list, but he's receiving
messages fine. So reply to the list and not to me. Message follows:
-------- Original Message -------
The original post:
Title: Update touches unrelated indexes!?
Hi Everyone,
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. The statement is this:
UPDATE schema_1.test_table SET col_27 = 0;
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.
I see these "extra" locks even if I drop the idx_test_table_col_27 index
before the update. The performance of this update is extremely slow. I'm
much better off if I drop all indexes before the update and recreate
them after the update. However, deleting these indexes has a negative
impact on the performance of other queries that are concurrently being
executed.
Is there a way to limit the impact of the update to the actual column
and index it is executed on?
Any help is greatly appreciated!
Regards,
Jozsef
dfdata=# \d test_table
Table "schema_1.test_table"
Column | Type | Modifiers
-----------------+-----------------------------+--------------------
col_1 | character varying | not null
col_2 | character varying |
col_3 | integer | not null
col_4 | integer | not null
col_5 | character varying | not null
col_6 | character varying | not null
col_7 | character(1) | not null
col_8 | character varying | not null
col_9 | character varying | not null
col_10 | character varying |
col_11 | bigint | not null
col_12 | integer | not null
col_13 | character varying |
col_14 | integer | not null
col_15 | character(38) | not null
col_16 | character varying | not null
col_17 | bigint | not null
col_18 | character varying |
col_19 | character varying |
col_20 | integer | not null
col_21 | integer | not null
col_22 | integer | not null
col_23 | integer | not null
col_24 | timestamp without time zone | not null
col_25 | timestamp without time zone | not null
col_26 | timestamp without time zone | not null
col_27 | integer | not null default 0
col_28 | integer | not null default 0
col_29 | integer | not null default 0
Indexes:
"idx_test_table_col_1" UNIQUE, btree (col_1)
"idx_test_table_col_27" btree (col_27)
"idx_test_table_col_14" btree (col_14)
"idx_test_table_col_12" btree (col_12)
"idx_test_table_col_24" btree (date_trunc('day'::text, col_24))
"idx_test_table_col_25" btree (date_trunc('day'::text, col_25))
"idx_test_table_col_26" btree (date_trunc('day'::text, col_26))
"idx_test_table_col_29" btree (col_29)
"idx_test_table_col_6" btree (col_6)
"idx_test_table_col_10" btree (lower(col_10::text))
"idx_test_table_col_10_2" btree (lower(col_10::text)
varchar_pattern_ops)
"idx_test_table_col_9" btree (lower(col_9::text))
"idx_test_table_col_9_2" btree (lower(col_9::text)
varchar_pattern_ops)
"idx_test_table_col_8" btree (lower(col_8::text))
"idx_test_table_col_8_2" btree (lower(col_8::text)
varchar_pattern_ops)
"idx_test_table_col_5" btree (col_5)
"idx_test_table_col_17" btree (col_17)
"idx_test_table_col_28" btree (col_28)
locktype | relation | mode | transaction | pid | granted |
nspname | relname
----------+----------+------------------+-------------+------+---------+
------------+-----------------------------------------------------
relation | 1259 | AccessShareLock | 73112 | 7923 | t |
pg_catalog | pg_class
relation | 10342 | AccessShareLock | 73112 | 7923 | t |
pg_catalog | pg_locks
relation | 2615 | AccessShareLock | 73112 | 7923 | t |
pg_catalog | pg_namespace
relation | 28344 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_27
relation | 28354 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_14
relation | 28353 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_12
relation | 28356 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_24
relation | 28357 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_25
relation | 28358 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_26
relation | 28346 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_29
relation | 28343 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_6
relation | 28351 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_10
relation | 28352 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_10_2
relation | 28349 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_9
relation | 28350 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_9_2
relation | 28347 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_8
relation | 28348 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_8_2
relation | 28341 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_1
relation | 28342 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_5
relation | 28355 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_17
relation | 28345 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | idx_test_table_col_28
relation | 27657 | AccessShareLock | 73109 | 7914 | t |
schema_1 | test_table
relation | 27657 | RowExclusiveLock | 73109 | 7914 | t |
schema_1 | test_table
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
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@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Friday, June 30, 2006 1:44 AM
To: Josh Berkus
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] FWD: Update touches unrelated indexes?
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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Jozsef Szalay wrote: > >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. Not at all -- the option is just continue to operate normally after the update, because all the indexes are always updated. If you see an index not being updated, it's a bug and by all means report it, preferably with a test case other people can reproduce. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
> >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.
>
When you update a table postgres makes a copy of the row being updated
so it has to create new index entries pointing to the new version of
the row... but it keeps old index entries pointing to the prior
version of the row because if there are concurrent queries to those
tables that looks for that particular row and you haven't committed
yet we still want the old version (old index entry)...
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook