From: | AI Rumman <rummandba(at)gmail(dot)com> |
---|---|
To: | pgsql-general General <pgsql-general(at)postgresql(dot)org> |
Subject: | increasing varchar column size is taking too much time |
Date: | 2015-02-14 02:42:10 |
Message-ID: | CAGoODpeDzBr2nk7qE-OQ0JMU_HsjgVVeLW7LP6xmKYiB3=Gv-w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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?
From | Date | Subject | |
---|---|---|---|
Next Message | Ramesh T | 2015-02-14 12:58:57 | Re: Collection |
Previous Message | Bill Moran | 2015-02-13 22:56:31 | Re: What's a reasonable maximum number for table partitions? |