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.
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)
density) that could be switched off temporarily? Did you try to execute the update in several steps?LOCK TABLE page_densities IN EXCLUSIVE MODEbefore the UPDATE statement. You might also consider: a) disabling all triggers on the table (if there are some); b) dropping all indexes and rebuilding them after the update