FWD: Update touches unrelated indexes? - Mailing list pgsql-performance

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