pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAdding and filling new column on big table
11+ messages / 5 participants
[nested] [flat]
* Adding and filling new column on big table
@ 2006-05-17 01:19 Jonathan Blitz <jb@anykey.co.il>
2006-05-17 02:31 ` Re: Adding and filling new column on big table Michael Fuhr <mike@fuhr.org>
2006-05-30 03:22 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
0 siblings, 2 replies; 11+ messages in thread
From: Jonathan Blitz @ 2006-05-17 01:19 UTC (permalink / raw)
To: pgsql-performance
I have a table of about 500,000 rows.
I need to add a new column and populate it.
So, I have tried to run the following command. The command never finishes (I
gave up after about and hour and a half!).
Note that none of the columns have indexes.
Update mytable set new_column =
case when column_1 = column_2
then 1
when column_1+column_3= column_2 and column_3 > 0
then 2
when column_1+column_3+column_4 = column_2 and column_4 > 0
then 3
when column_1+column_3+column_4+column_5 = column_2 and column_5 > 0
then 4
else
0
end
My computer is a Pentium 4 2.4 GHZ and 1G RAM so it should be fast
enough.
Any ideas?
Jonathan Blitz
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/340 - Release Date: 05/15/2006
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Adding and filling new column on big table
2006-05-17 01:19 Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
@ 2006-05-17 02:31 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 11+ messages in thread
From: Michael Fuhr @ 2006-05-17 02:31 UTC (permalink / raw)
To: Jonathan Blitz <jb@anykey.co.il>; +Cc: pgsql-performance
On Wed, May 17, 2006 at 03:19:26AM +0200, Jonathan Blitz wrote:
> I have a table of about 500,000 rows.
>
> I need to add a new column and populate it.
>
> So, I have tried to run the following command. The command never finishes (I
> gave up after about and hour and a half!).
If you install contrib/pgstattuple you can figure out how fast the
update is running. Run "SELECT * FROM pgstattuple('mytable')" a
few times and note the rate at which dead_tuple_count is increasing.
If it's not increasing at all then query pg_locks and look for locks
where "granted" is false.
I created a test table, populated it with 500,000 rows of random
data, and ran the update you posted. On a 500MHz Pentium III with
512M RAM and a SCSI drive from the mid-to-late 90s, running PostgreSQL
8.1.3 on FreeBSD 6.1, the update finished in just over two minutes.
The table had one index (the primary key).
> Note that none of the columns have indexes.
Do you mean that no columns in the table have indexes? Or that the
columns referenced in the update don't have indexes but that other
columns do? What does "\d mytable" show? Do other tables have
foreign key references to this table? What non-default settings
do you have in postgresql.conf? What version of PostgreSQL are you
running and on what platform? How busy is the system? What's the
output of "EXPLAIN UPDATE mytable ..."?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Adding and filling new column on big table
2006-05-17 01:19 Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
@ 2006-05-30 03:22 ` Francisco Reyes <lists@stringsutils.com>
2006-05-30 09:35 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
1 sibling, 1 reply; 11+ messages in thread
From: Francisco Reyes @ 2006-05-30 03:22 UTC (permalink / raw)
To: Jonathan Blitz <jb@anykey.co.il>; +Cc: pgsql-performance
Jonathan Blitz writes:
> So, I have tried to run the following command. The command never finishes
> (I gave up after about and hour and a half!).
Did you ever find what was the problem?
Perhaps you needed to run a vacuum full on the table?
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Adding and filling new column on big table
2006-05-17 01:19 Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 03:22 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
@ 2006-05-30 09:35 ` Jonathan Blitz <jb@anykey.co.il>
2006-05-30 15:58 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
0 siblings, 1 reply; 11+ messages in thread
From: Jonathan Blitz @ 2006-05-30 09:35 UTC (permalink / raw)
To: 'Francisco Reyes' <lists@stringsutils.com>; +Cc: pgsql-performance
> > So, I have tried to run the following command. The command never
finishes
> > (I gave up after about and hour and a half!).
>
> Did you ever find what was the problem?
> Perhaps you needed to run a vacuum full on the table?
Nope.
I just gave up in the end and left it with NULL as the default value.
There were, in fact, over 2 million rows in the table rather than 1/4 of a
million so that was part of the problem.
Jonathan
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.4/351 - Release Date: 05/29/2006
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Adding and filling new column on big table
2006-05-17 01:19 Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 03:22 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
2006-05-30 09:35 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
@ 2006-05-30 15:58 ` Francisco Reyes <lists@stringsutils.com>
2006-05-30 17:11 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
0 siblings, 1 reply; 11+ messages in thread
From: Francisco Reyes @ 2006-05-30 15:58 UTC (permalink / raw)
To: Jonathan Blitz <jb@anykey.co.il>; +Cc: pgsql-performance
Jonathan Blitz writes:
> I just gave up in the end and left it with NULL as the default value.
Could you do the updates in batches instead of trying to do them all at
once?
Have you done a vacuum full on this table ever?
> There were, in fact, over 2 million rows in the table rather than 1/4 of a
> million so that was part of the problem.
What hardware?
I have a dual CPU opteron with 4GB of RAM and 8 disks in RAID 10 (SATA).
Doing an update on a 5 million record table took quite a while, but it did
fininish. :-)
I just did vacuum full before and after though.. That many updates tend to
slow down operations on the table aftewards unless you vacuum the table.
Based on what you wrote it sounded as if you tried a few times and may have
killed the process.. this would certainly slow down the operations on that
table unless you did a vacuum full.
I wonder if running vacuum analyze against the table as the updates are
running would be of any help.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Adding and filling new column on big table
2006-05-17 01:19 Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 03:22 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
2006-05-30 09:35 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 15:58 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
@ 2006-05-30 17:11 ` Jonathan Blitz <jb@anykey.co.il>
2006-05-30 17:25 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
0 siblings, 1 reply; 11+ messages in thread
From: Jonathan Blitz @ 2006-05-30 17:11 UTC (permalink / raw)
To: 'Francisco Reyes' <lists@stringsutils.com>; +Cc: pgsql-performance
>
>
> Could you do the updates in batches instead of trying to do them all at
> once?
Nope. Didn't think it would make any difference.
>
> Have you done a vacuum full on this table ever?
Many times
>
> What hardware?
> I have a dual CPU opteron with 4GB of RAM and 8 disks in RAID 10 (SATA).
> Doing an update on a 5 million record table took quite a while, but it did
> fininish. :-)
I am using a laptop :).
Pentium 4 (not 4M) with 1GB of memory - 2 MHZ
Must do it on that since the program is aimed for use at home.
Jonathan
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.4/351 - Release Date: 05/29/2006
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Adding and filling new column on big table
2006-05-17 01:19 Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 03:22 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
2006-05-30 09:35 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 15:58 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
2006-05-30 17:11 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
@ 2006-05-30 17:25 ` Francisco Reyes <lists@stringsutils.com>
2006-05-30 21:04 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
0 siblings, 1 reply; 11+ messages in thread
From: Francisco Reyes @ 2006-05-30 17:25 UTC (permalink / raw)
To: Jonathan Blitz <jb@anykey.co.il>; +Cc: pgsql-performance
Jonathan Blitz writes:
> Nope. Didn't think it would make any difference.
May be worth a try.
> I am using a laptop :).
> Pentium 4 (not 4M) with 1GB of memory - 2 MHZ
Most laptop drives are only 5,400 RPM which would make a transaction like
you are describing likely take a while.
> Must do it on that since the program is aimed for use at home.
No desktop at home you could try it on?
I think the problem with the laptop is likely it's drive.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Adding and filling new column on big table
2006-05-17 01:19 Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 03:22 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
2006-05-30 09:35 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 15:58 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
2006-05-30 17:11 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 17:25 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
@ 2006-05-30 21:04 ` Jonathan Blitz <jb@anykey.co.il>
2006-05-30 20:07 ` Re: Adding and filling new column on big table Scott Marlowe <smarlowe@g2switchworks.com>
2006-05-30 20:24 ` Re: Adding and filling new column on big table PFC <lists@peufeu.com>
2006-05-30 20:52 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
0 siblings, 3 replies; 11+ messages in thread
From: Jonathan Blitz @ 2006-05-30 21:04 UTC (permalink / raw)
To: 'Francisco Reyes' <lists@stringsutils.com>; +Cc: pgsql-performance
>
> Most laptop drives are only 5,400 RPM which would make a transaction like
> you are describing likely take a while.
Not sure what my one is but it is new(ish).
>
> No desktop at home you could try it on?
> I think the problem with the laptop is likely it's drive.
I suppose I could do but I need to install PostgreSQL there and then copy
over the database.
Maybe I will give it a try.
Jonathan
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.7.4/351 - Release Date: 05/29/2006
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Adding and filling new column on big table
2006-05-17 01:19 Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 03:22 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
2006-05-30 09:35 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 15:58 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
2006-05-30 17:11 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 17:25 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
2006-05-30 21:04 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
@ 2006-05-30 20:07 ` Scott Marlowe <smarlowe@g2switchworks.com>
2 siblings, 0 replies; 11+ messages in thread
From: Scott Marlowe @ 2006-05-30 20:07 UTC (permalink / raw)
To: Jonathan Blitz <jb@anykey.co.il>; +Cc: Francisco Reyes <lists@stringsutils.com>; pgsql-performance
On Tue, 2006-05-30 at 16:04, Jonathan Blitz wrote:
> >
> > Most laptop drives are only 5,400 RPM which would make a transaction like
> > you are describing likely take a while.
>
> Not sure what my one is but it is new(ish).
>
> >
> > No desktop at home you could try it on?
> > I think the problem with the laptop is likely it's drive.
>
> I suppose I could do but I need to install PostgreSQL there and then copy
> over the database.
Keep in mind, most, if not all IDE drives lie about fsync, so the speed
of the drive is a limit only if you're actually writing a lot of data.
If you're doing a lot of little transactions, the drive should be lying
and holding the data in cache on board, so the speed should be OK.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Adding and filling new column on big table
2006-05-17 01:19 Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 03:22 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
2006-05-30 09:35 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 15:58 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
2006-05-30 17:11 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 17:25 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
2006-05-30 21:04 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
@ 2006-05-30 20:24 ` PFC <lists@peufeu.com>
2 siblings, 0 replies; 11+ messages in thread
From: PFC @ 2006-05-30 20:24 UTC (permalink / raw)
To: Jonathan Blitz <jb@anykey.co.il>; 'Francisco Reyes' <lists@stringsutils.com>; +Cc: pgsql-performance
>> Most laptop drives are only 5,400 RPM which would make a transaction
>> like
>> you are describing likely take a while.
>
> Not sure what my one is but it is new(ish).
If you're doing data intensive operations (like a big update which looks
like what you're doing) it will write many megabytes to the harddrive...
my laptop HDD (5400 rpm) does about 15 MB/s throughput while a standard
desktop 7200rpm drive does 55-60 MB/s throughput. Plus, seek times on a
laptop drive are horrendous.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Adding and filling new column on big table
2006-05-17 01:19 Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 03:22 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
2006-05-30 09:35 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 15:58 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
2006-05-30 17:11 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-30 17:25 ` Re: Adding and filling new column on big table Francisco Reyes <lists@stringsutils.com>
2006-05-30 21:04 ` Re: Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
@ 2006-05-30 20:52 ` Francisco Reyes <lists@stringsutils.com>
2 siblings, 0 replies; 11+ messages in thread
From: Francisco Reyes @ 2006-05-30 20:52 UTC (permalink / raw)
To: Jonathan Blitz <jb@anykey.co.il>; +Cc: pgsql-performance
Jonathan Blitz writes:
> I suppose I could do but I need to install PostgreSQL there and then copy
> over the database.
> Maybe I will give it a try.
I really think that is your best bet.
If for whatever reason that will not be an option perhaps you can just let
the process run over the weekend.. possibly monitor the process from the OS
to make sure it is not frozen.
Don't recall if you mentioned the OS.. is it any unix like os?
If so there are several ways you could check to make sure the process is not
frozen such as iostats, top, vmstats(these from FreeBSD, but most unix like
os should have tools like those if not some with the same name).
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2006-05-30 21:04 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-05-17 01:19 Adding and filling new column on big table Jonathan Blitz <jb@anykey.co.il>
2006-05-17 02:31 ` Michael Fuhr <mike@fuhr.org>
2006-05-30 03:22 ` Francisco Reyes <lists@stringsutils.com>
2006-05-30 09:35 ` Jonathan Blitz <jb@anykey.co.il>
2006-05-30 15:58 ` Francisco Reyes <lists@stringsutils.com>
2006-05-30 17:11 ` Jonathan Blitz <jb@anykey.co.il>
2006-05-30 17:25 ` Francisco Reyes <lists@stringsutils.com>
2006-05-30 21:04 ` Jonathan Blitz <jb@anykey.co.il>
2006-05-30 20:07 ` Scott Marlowe <smarlowe@g2switchworks.com>
2006-05-30 20:24 ` PFC <lists@peufeu.com>
2006-05-30 20:52 ` Francisco Reyes <lists@stringsutils.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox