expand a varchar column very slowly , why?

Posted by francs on Stack Overflow See other posts from Stack Overflow or by francs
Published on 2011-02-15T11:49:25Z Indexed on 2011/02/15 15:25 UTC
Read the original article Hit count: 250

Filed under:

Hi We need to modify a column of a big product table , usually normall ddl statments will be excutely fast ,but the above ddl statmens takes about 10 minnutes?I wonder know the reason! I just want to expand a varchar column?The following is the detailsl

--table size
wapreader_log=> select pg_size_pretty(pg_relation_size('log_foot_mark'));
 pg_size_pretty 
----------------
 5441 MB
(1 row)


--table ddl
wapreader_log=> \d log_foot_mark
          Table "wapreader_log.log_foot_mark"
   Column    |            Type             | Modifiers 
-------------+-----------------------------+-----------
 id          | integer                     | not null
 create_time | timestamp without time zone | 
 sky_id      | integer                     | 
 url         | character varying(1000)     | 
 refer_url   | character varying(1000)     | 
 source      | character varying(64)       | 
 users       | character varying(64)       | 
 userm       | character varying(64)       | 
 usert       | character varying(64)       | 
 ip          | character varying(32)       | 
 module      | character varying(64)       | 
 resource_id | character varying(100)      | 
 user_agent  | character varying(128)      | 
Indexes:
    "pk_log_footmark" PRIMARY KEY, btree (id)


--alter column
wapreader_log=> \timing
Timing is on.

wapreader_log=>  ALTER TABLE wapreader_log.log_foot_mark ALTER column user_agent TYPE character varying(256); 
ALTER TABLE
Time: 603504.835 ms    

© Stack Overflow or respective owner

Related posts about postgresql