Re: Updates on large tables are extremely slow

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.