From: Yves Vindevogel <yves.vindevogel@implements.be>
To: pgsql-performance@postgresql.org
Subject: Updates on large tables are extremely slow
Date: Sun, 12 Jun 2005 19:40:29 +0200
Message-ID: <579bc853ef3957c736c2773f13c444f7@implements.be> (raw)
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
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.
Attachments:Pasted Graphic 2.tiff (image/tiff, 5.6K) downloadview image
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: yves.vindevogel@implements.be
Subject: Re: Updates on large tables are extremely slow
In-Reply-To: <579bc853ef3957c736c2773f13c444f7@implements.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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