How can I optimize this PostgreSQL query that updates every row?

Question

I wrote a query to update entire table. How can I improve this query to take less time:

update page_densities set density = round(density - 0.001, 2)

Query returned successfully: 628391 rows affected, 1754179 ms (29 minutes) execution time.

EDIT: By setting work memory..

set work_mem = '500MB';
update page_densities set density = round(density - 0.001, 2)

Query returned successfully: 628391 rows affected, 731711 ms (12 minutes) execution time.

Answer 1

Assuming density is not an index, you may be able to improve performance with a different fillfactor. See this question/answer or the PostgreSQL docs for more info:

http://www.postgresql.org/docs/9.4/static/sql-createtable.html

Slow simple update query on PostgreSQL database with 3 million rows

Although you cannot modify a table's fillfactor, you can create a new table with a different fill factor and copy the data over. Here is some sample code.

--create a new table with a different fill factor
CREATE TABLE page_densities_new
(
 ...some fields here
)
WITH (
  FILLFACTOR=70
);

--copy all of the records into the new table
insert into page_densities_new select * from page_densities;

--rename the original/old table
ALTER TABLE page_densities RENAME TO page_densities_old;

--rename the new table
ALTER TABLE page_densities_new RENAME TO page_densities;

After this you have a table with the same name and data as the original, but it has a different fill factor. I set it to 70, but it can be any value 10 to 100. (100 is the default)