Thread: update performance 8.1.4

update performance 8.1.4

From
Thomas Markus
Date:
Hi,

How can I speed up updates?
i try an
  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

thanks
thomas






--
Thomas Markus

Tel:    +49 30 29 36 399 - 22
Fax:    +49 30 29 36 399 - 50
Mail:   t.markus@proventis.net
Web:    http://www.proventis.net
Web:    http://www.blue-ant.de

proventis GmbH
Zimmerstraße 79-80
10117 Berlin

Geschäftsführer: Norman Frischmuth
Sitz: Berlin
Handelsregister: AG Berlin-Charlottenburg, HR 82917

We support your project business!


Attachment

Re: update performance 8.1.4

From
"Scott Marlowe"
Date:
2008/1/15 Thomas Markus <t.markus@proventis.net>:
> Hi,
>
> How can I speed up updates?
> i try an
>   update tablename set datecol=null
> ~ 2.3 mio rows

Got any foreign keys against that table?  If so, they need to be
indexed on the other end.

What's the schema of that table and any related tables?

Re: update performance 8.1.4

From
"Kevin Grittner"
Date:
>>> On Tue, Jan 15, 2008 at  3:33 AM, in message <478C7DEC.1020806@proventis.net>,
Thomas Markus <t.markus@proventis.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