Skip site navigation (1) Skip section navigation (2)

add column with default value is very slow

From: AI Rumman <rummandba(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: add column with default value is very slow
Date: 2012-09-11 13:20:28
Message-ID: CAGoODpdBTkhXDuOQDLxuEJYrWfsZZCi_tkjgzDyzPRGDHeQ9ag@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
I have a table as follows:
\d entity
                 Table "public.entity"
    Column    |            Type             |     Modifiers
--------------+-----------------------------+--------------------
 crmid        | integer                     | not null
 smcreatorid  | integer                     | not null default 0
 smownerid    | integer                     | not null default 0
 modifiedby   | integer                     | not null default 0
 setype       | character varying(30)       | not null
 description  | text                        |
 createdtime  | timestamp without time zone | not null
 modifiedtime | timestamp without time zone | not null
 viewedtime   | timestamp without time zone |
 status       | character varying(50)       |
 version      | integer                     | not null default 0
 presence     | integer                     | default 1
 deleted      | integer                     | not null default 0
Indexes:
    "entity_pkey" PRIMARY KEY, btree (crmid)
    "entity_createdtime_idx" btree (createdtime)
    "entity_modifiedby_idx" btree (modifiedby)
    "entity_modifiedtime_idx" btree (modifiedtime)
    "entity_setype_idx" btree (setype) WHERE deleted = 0
    "entity_smcreatorid_idx" btree (smcreatorid)
    "entity_smownerid_idx" btree (smownerid)
    "ftx_en_entity_description" gin (to_tsvector('vcrm_en'::regconfig,
for_fts(description)))
    "entity_deleted_idx" btree (deleted)
Referenced by:
    TABLE "service" CONSTRAINT "fk_1_service" FOREIGN KEY (serviceid)
REFERENCES entity(crmid) ON DELETE CASCADE
    TABLE "servicecontracts" CONSTRAINT "fk_1_servicecontracts" FOREIGN KEY
(servicecontractsid) REFERENCES entity(crmid) ON DELETE CASCADE
    TABLE "vantage_cc2entity" CONSTRAINT "fk_vantage_cc2entity_entity"
FOREIGN KEY (crm_id) REFERENCES entity(crmid) ON UPDATE CASCADE ON DELETE
CASCADE
    TABLE "vantage_emails_optout_history" CONSTRAINT
"fk_vantage_emails_optout_history_crmid" FOREIGN KEY (crmid) REFERENCES
entity(crmid) ON DELETE CASCADE
    TABLE "vantage_emails_optout_history" CONSTRAINT
"fk_vantage_emails_optout_history_emailid" FOREIGN KEY (emailid) REFERENCES
entity(crmid) ON DELETE CASCADE

I execued the query:
ALTER TABLE entity ADD COLUMN owner_type char(1) NOT NULL default 'U';

The db is stuck. The enity table has 2064740 records;

Watching locks:
select
 pg_stat_activity.datname,pg_class.relname,pg_locks.mode, pg_locks.granted,
pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,10),
pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid
from pg_stat_activity,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid order by query_start;


      datname      |               relname               |        mode
    | granted | usename  |   substr   |          query_start          |
  age       | procpid
-------------------+-------------------------------------+---------------------+---------+----------+------------+-------------------------------+-----------------+---------
 db_test | entity_modifiedtime_idx          | AccessExclusiveLock | t
| user     | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
  13574
 db_test |                                     | ExclusiveLock       | t
    | user     | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 |   13574
 db_test | entity_modifiedby_idx            | AccessExclusiveLock | t
| user     | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
  13574
 db_test | entity_createdtime_idx           | AccessExclusiveLock | t
| user     | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
  13574
 db_test | entity                    | ShareLock           | t       | user
    | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |   13574
 db_test | entity                    | AccessExclusiveLock | t       | user
    | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |   13574
 db_test |                                     | AccessExclusiveLock | t
    | user     | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 |   13574
 db_test |                                     | AccessExclusiveLock | t
    | user     | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 |   13574
 db_test |                                     | ExclusiveLock       | t
    | user     | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 |   13574
 db_test | entity_pkey               | AccessExclusiveLock | t       | user
    | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |   13574
 db_test |                                     | ShareLock           | t
    | user     | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 |   13574
 db_test | ftx_en_entity_description | AccessExclusiveLock | t       | user
    | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |   13574
 db_test |                                     | AccessShareLock     | t
    | user     | ALTER TABL | 2012-09-11 12:26:20.269965+06 |
00:45:46.101971 |   13574
 db_test | entity_smcreatorid_idx           | AccessExclusiveLock | t
| user     | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
  13574
 db_test | entity_smownerid_idx             | AccessExclusiveLock | t
| user     | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
  13574
 db_test | entity_setype_idx                | AccessExclusiveLock | t
| user     | ALTER TABL | 2012-09-11 12:26:20.269965+06 | 00:45:46.101971 |
  13574

Any idea for the db stuck?

Responses

pgsql-performance by date

Next:From: Albe LaurenzDate: 2012-09-11 13:41:04
Subject: Re: add column with default value is very slow
Previous:From: Bill MartinDate: 2012-09-11 07:20:24
Subject: Re: Planner selects different execution plans depending on limit

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group