From: Jacques Caron <jc@directinfos.com>
To: Yves Vindevogel <yves.vindevogel@implements.be>
Cc: pgsql-performance@postgresql.org
Subject: Re: Updates on large tables are extremely slow
Date: Sun, 12 Jun 2005 19:57:54 +0200
Message-ID: <6.2.0.14.0.20050612195157.039cd170@pop.interactivemediafactory.net> (raw)
In-Reply-To: <579bc853ef3957c736c2773f13c444f7@implements.be>
References: <579bc853ef3957c736c2773f13c444f7@implements.be>
Hi,
At 19:40 12/06/2005, Yves Vindevogel wrote:
>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)
An index on (-pages) would probably do exactly what you want without having
to add another column.
>The query takes about half an hour to an hour to execute.
Depending on the total size of the table and associated indexes and on your
exact setup (especially your hardare), this could be quite normal: the
exuctor goes through all rows in the table, and for each, creates a copy
with the additional column, updates indexes, and logs to WAL. You might
want to look into moving your WAL files (pg_xlog) to a separate disk,
increase WAL and checkpoint buffers, add more RAM, add more disks...
But as I said, you might not even need to do that, just use an index on an
expression...
Jacques.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: jc@directinfos.com, yves.vindevogel@implements.be
Subject: Re: Updates on large tables are extremely slow
In-Reply-To: <6.2.0.14.0.20050612195157.039cd170@pop.interactivemediafactory.net>
References: <579bc853ef3957c736c2773f13c444f7@implements.be> <6.2.0.14.0.20050612195157.039cd170@pop.interactivemediafactory.net>
* 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