add column with default value is very slow

Поиск
Список
Период
Сортировка
От AI Rumman
Тема add column with default value is very slow
Дата
Msg-id CAGoODpdBTkhXDuOQDLxuEJYrWfsZZCi_tkjgzDyzPRGDHeQ9ag@mail.gmail.com
обсуждение исходный текст
Ответы Re: add column with default value is very slow
Re: add column with default value is very slow
Список 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?   

В списке pgsql-performance по дате отправления:

Предыдущее
От: Bill Martin
Дата:
Сообщение: Re: Planner selects different execution plans depending on limit
Следующее
От: "Albe Laurenz"
Дата:
Сообщение: Re: add column with default value is very slow