increasing varchar column size is taking too much time
| От | AI Rumman |
|---|---|
| Тема | increasing varchar column size is taking too much time |
| Дата | |
| Msg-id | CAGoODpeDzBr2nk7qE-OQ0JMU_HsjgVVeLW7LP6xmKYiB3=Gv-w@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: increasing varchar column size is taking too much time
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| Список | pgsql-general |
Hi,
I started the following query in Postgresql 9.1 where only this sql is running on the host and it has been taking more than an hour and still running.
alter table userdata.table1 alter column name type varchar(512);
Here is the table description:
d+ userdata.table1
Table "userdata.table1"
Column | Type | Modifiers | Storage | Description
---------------------+-----------------------------+------------------------+----------+-------------------------
id | character varying(50) | not null | extended |
code | character varying(32) | not null | extended |
accid | character varying(50) | not null | extended |
name | character varying(100) | not null | extended |
create_time | timestamp with time zone | not null default now() | plain |
modified_time | timestamp with time zone | not null default now() | plain |
install_date | timestamp without time zone | | plain |
recent_scan_date | timestamp without time zone | | plain |
update_date | timestamp without time zone | | plain |
setting | character varying(100) | | extended |
name | character varying(100) | | extended |
type | character varying(8) | | extended |
version | character varying(128) | | extended |
package | character varying(255) | | extended |
permission | text | | extended |
trigger | character varying(10) | | extended |
reasons | character varying(200) | | extended |
note | character varying(255) | | extended |
size | bigint | | plain |
usage | bigint | | plain |
running | character varying(4) | | extended |
location | character varying(60) | | extended |
can_stop | character(1) | | extended |
can_uninstall | character(1) | | extended |
flagged_status | character(1) | | extended |
status | character(1) | | extended |
consultation_status | character(1) | | extended |
trust | character(1) | | extended |
Indexes:
"table1_pk" PRIMARY KEY, btree (id, code)
"table1_accid_id_hashcode_idx" btree (accid, id, code)
"table1_accid_idx" btree (accid)
"table1_id_idx" btree (id)
Triggers:
table1s_delete_trigger BEFORE DELETE ON table1 FOR EACH ROW EXECUTE PROCEDURE delete_jangles_table1()
table1s_insert_trigger BEFORE INSERT ON table1 FOR EACH ROW EXECUTE PROCEDURE insert_jangles_table1()
Child tables: table1_0,
table1_1,
table1_10,
table1_2,
table1_3,
table1_4,
table1_5,
table1_6,
table1_7,
table1_8,
table1_9
Has OIDs: no
Here are the number of rows and pages in partition:
relname | reltuples | relpages
---------------+-----------+----------
table1_0 | 10076840 | 362981
table1_1 | 10165073 | 366548
table1_2 | 10046372 | 361838
table1_3 | 10114727 | 364360
table1_4 | 10155816 | 366054
table1_5 | 10188953 | 367023
table1_6 | 10275270 | 370887
table1_7 | 10163937 | 366245
table1_8 | 10262516 | 369350
table1_9 | 10359893 | 372099
table1_10 | 10434026 | 375327
table1 | 0 | 0
Any idea why the above ALTER statement is taking that much time?
Is it because of the number of rows we have in each partition?
Any suggestion for it?
В списке pgsql-general по дате отправления:
Предыдущее
От: Bill MoranДата:
Сообщение: Re: What's a reasonable maximum number for table partitions?
Следующее
От: Berend ToberДата:
Сообщение: Re: How to hide stored procedure's bodies from specific user