At 19:40 12/06/2005, Yves Vindevogel wrote:

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...


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?


Reply via email to