Updates on large tables are extremely slow - Mailing list pgsql-performance

Hi,


I'm trying to update a table that has about 600.000 records.

The update query is very simple  :    update mytable set pagesdesc = -
pages ;


(I use pagesdesc to avoid problems with sort that have one field in
ascending order and one in descending order.  That was a problem I had
a week ago)


The query takes about half an hour to an hour to execute.  I have
tried a lot of things.

This is my setup


Linux Slackware 10.1

Postgres 8.0.1

My filesystem has EXT2 filesystem so I don't have journaling.

My partition is mounted in fstab with the noatime option.


I have tried to change some settings in $PGDATA/postgresql.conf.  But
that does not seem to matter a lot.

I'm not even sure that file is being used.  I ran KSysGuard when
executing my query and I don't see my processor being used more than
20%

The memory increases for the cache, but not for the app itself.


My testsystem is an Asus portable, P4 with 1 Gig of RAM.

Disk is speedy.  All runs fine except for the update queries.


I would appreciate some help or a document to point me to the settings
I must change.


Met vriendelijke groeten,

Bien à vous,

Kind regards,


<bold>Yves Vindevogel</bold>

<bold>Implements</bold>

<smaller>

</smaller>Hi,

I'm trying to update a table that has about 600.000 records.
The update query is very simple  :    update mytable set pagesdesc = -
pages ;

(I use pagesdesc to avoid problems with sort that have one field in
ascending order and one in descending order.  That was a problem I had
a week ago)

The query takes about half an hour to an hour to execute.  I have tried
a lot of things.
This is my setup

Linux Slackware 10.1
Postgres 8.0.1
My filesystem has EXT2 filesystem so I don't have journaling.
My partition is mounted in fstab with the noatime option.

I have tried to change some settings in $PGDATA/postgresql.conf.  But
that does not seem to matter a lot.
I'm not even sure that file is being used.  I ran KSysGuard when
executing my query and I don't see my processor being used more than
20%
The memory increases for the cache, but not for the app itself.

My testsystem is an Asus portable, P4 with 1 Gig of RAM.
Disk is speedy.  All runs fine except for the update queries.

I would appreciate some help or a document to point me to the settings
I must change.

Met vriendelijke groeten,
Bien à vous,
Kind regards,

Yves Vindevogel
Implements

<smaller>


Mail: yves.vindevogel@implements.be  - Mobile: +32 (478) 80 82 91


Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76


Web: http://www.implements.be

<italic><x-tad-smaller>

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.

Mahatma Ghandi.</x-tad-smaller></italic></smaller>


Mail: yves.vindevogel@implements.be  - Mobile: +32 (478) 80 82 91

Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76

Web: http://www.implements.be

First they ignore you.  Then they laugh at you.  Then they fight you.
Then you win.
Mahatma Ghandi.