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

Question

I wrote a query to update the whole table. How can I improve this query to shorten the time:

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

The request returned successfully: 628391 affected lines, 1754179 ms (29 minutes) execution time.

EDIT: By installing working memory ..

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

The request returned successfully: 628391 affected lines, 731711 ms (12 minutes) of execution time.

Answer 1

Assuming density is not an index, you can improve performance with another fillfactor. See this question / answer or PostgreSQL docs for more information:

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

Slow, simple query to update a PostgreSQL database with 3 million rows

Although you cannot change the fill table file, you can create a new table with a different fill factor and copy the data. Here is a 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 that, 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 from 10 to 100. (100 by default)