Skip site navigation (1) Skip section navigation (2)

Re: update performance 8.1.4

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <pgsql-admin(at)postgresql(dot)org>,"Thomas Markus" <t(dot)markus(at)proventis(dot)net>
Subject: Re: update performance 8.1.4
Date: 2008-01-18 23:04:54
Message-ID: 4790DC36.EE98.0025.0@wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-admin
>>> On Tue, Jan 15, 2008 at  3:33 AM, in message <478C7DEC(dot)1020806(at)proventis(dot)net>,
Thomas Markus <t(dot)markus(at)proventis(dot)net> wrote: 
 
>   update tablename set datecol=null
> ~ 2.3 mio rows
> 
> After 6 hours, this was still not finished. selects and inserts are ok.
> System is a dual xeon, 8gb ram, debian 64bit, pg 8.1.4
 
You do realize that every row updated is a delete and an insert,
right?  So this should take as long as a single database transaction
which deleted all the rows in the table and inserted them again.
 
If datecol is null in any rows, you should try:
 
update tablename set datecol=null where datecol is not null;
 
If you have canceled the update because of the long run time, you
will have left a lot of dead rows in the table, which will make the
next attempt run even longer.  Be sure you are doing vacuums.
 
-Kevin
 



In response to

pgsql-admin by date

Next:From: Guillermo AriasDate: 2008-01-21 23:49:55
Subject: how to create a limited user
Previous:From: Carol WalterDate: 2008-01-18 17:54:18
Subject: Re: Can not access the public schema

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group