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
