Thank you for the response... to be perfectly honest, I don't know enough to know what I'm not telling you. Below is the string I use to create the table, so you can see the contents. I don't think I have foreign key references or triggers of any kind. Any ideas? (this is 8.3 running on Mac OS 10.7)
Thanks again, Eric [tableString setString:@""]; [tableString appendString:@"create table images (\"imageID\" varchar(11) primary key,"]; [tableString appendString:@"\"patientID\" varchar(11) null,"]; [tableString appendString:@"\"layoutID\" varchar(11) null,"]; for( iTooth = 0; iTooth < 33; iTooth++ ){ [tableString appendString:[NSString stringWithFormat:@"tooth_%d varchar(1) default 0,",iTooth]]; } [tableString appendString:@"\"pixelsWide\" varchar(4) null,"]; [tableString appendString:@"\"pixelsHigh\" varchar(4) null,"]; [tableString appendString:@"\"bytesPerPixel\" varchar(1) null,"]; [tableString appendString:@"\"imageData\" bytea null,"]; [tableString appendString:@"\"filePath\" varchar(256) null,"]; [tableString appendString:@"orientation char(1) null,"]; [tableString appendString:@"sequence char(2) null,"]; [tableString appendString:@"\"genericInfo\" varchar(65536),"]; [tableString appendString:@"time time null,"]; [tableString appendString:@"\"saveState\" varchar(1) default \'0\',"]; [tableString appendString:@"date date null)"]; On Oct 21, 2011, at 8:24 PM, Tom Lane wrote: > Eric Smith <eric_h_sm...@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 way to 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