Re: adding a column takes FOREVER! - Mailing list pgsql-general

From Tom Lane
Subject Re: adding a column takes FOREVER!
Date
Msg-id 28828.1319253855@sss.pgh.pa.us
Whole thread Raw
In response to adding a column takes FOREVER!  (Eric Smith <eric_h_smith@mac.com>)
Responses Re: adding a column takes FOREVER!
Re: adding a column takes FOREVER!
List pgsql-general
Eric Smith <eric_h_smith@mac.com> writes:
> I'm adding a column in postgres 8.3 with the syntax:  alter table images add column "saveState" varchar(1) default
'0'; It takes a good solid 20 minutes to add this column to a table with ~ 14,000 entries.  Why so long?  Is there a
wayto speed that up?  The table has ~ 50 columns. 

As Craig explained, that does require updating every row ... but for
only 14000 rows, it doesn't seem like it should take that long.
A quick test with 8.3 on my oldest and slowest machine:

regression=# create table foo as select generate_series(1,14000) as x;
SELECT
Time: 579.518 ms
regression=# alter table foo add column "saveState" varchar(1) default '0';
ALTER TABLE
Time: 482.143 ms

I'm thinking there is something you haven't told us about that creates a
great deal of overhead for updates on this table.  Lots and lots o'
indexes?  Lots and lots o' foreign key references?  Inefficient
triggers?

Or maybe it's just blocking behind somebody else's lock?

            regards, tom lane

pgsql-general by date:

Previous
From: Eduardo Morras
Date:
Subject: Re: PostGIS in a commercial project
Next
From: stefan
Date:
Subject: 9.0: plpgsql eror when restoring a database as a non superuser