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.

Any constraints involved (on `density`) that could be switched off temporarily? Did you try to execute the update in several steps? — Trinimon, May 12 '15 at 11:14
there is no constraint. can you please elaborate how to execute in several steps ? — Abdul Baig, May 12 '15 at 11:15
Are there any indexes on the table? Was the table in use by other users at the same time? Is the hard drive on which the table is stored being hit hard by other activity? — Gary - Stand with Ukraine, May 12 '15 at 11:17
yes on hard drive, there is no index, not the table is in use. — Abdul Baig, May 12 '15 at 11:19
Declare a cursor, and run through records in a loop. Do a `commit` after every 1000 records. This way you can work around potential IO issues. — Trinimon, May 12 '15 at 11:20
@Trinimon: that is going to be a lot slower that using a single update statement. — , May 12 '15 at 11:28
@G.B: updating 628391 in 29 minutes is awfully slow. My guess is that your harddisk is too slow - maybe your database is located on a network drive? Or an USB drive? I have a relatively slow server that is running inside a VM - it takes about 8 seconds to update 5 million rows. — , May 12 '15 at 11:30
its my personal system where i have the database. and i am updating all these records locally on my development enviroment — Abdul Baig, May 12 '15 at 11:35
Then there is something terribly wrong with your "personal sysstem". Even on a slow harddisk this shouldn't take that long. Please add the output of `explain (analyze, verbose) update ...` to your question. — , May 12 '15 at 11:36
@ _a_horse_with_no_name_: I'm not 100% familiar how it works with PostgreSQL, but I know that it might slow down other database, if too many records have to be stored for a potential rollback. This is at least valid for ORACLE and MySQL. Updating some (ten/hundred) thousand records by and by can be a solution. But it depends as well on the hardware, of course. — Trinimon, May 12 '15 at 12:05
@Trinimon: Oracle too will be slower when you commit frequently: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:4951966319022 "*Frequently commiting in the mistaken belief that you are conserving resources or making things go faster only does this for you: slows you down, yes, that is right, SLOWS YOU DOWN*" — , May 12 '15 at 12:10
Just as a test can you check it by adding `WHERE NOT (density = round(density - 0.001, 2))`, As a guess I think that seek will reduce clustered rows for update ;). — shA.t, May 12 '15 at 12:16
@ _a_horse_with_no_name_: somewhat too much to read :) but I doubt that this is valid for _all_ cases. However, according to your example above 628391records shouldn't be an issue at all ... — Trinimon, May 12 '15 at 12:16
@G.B.: if you want to update all rows of that table, you could try to recreate it with something like `create table new_page_densities as select ..., round(density - 0.001, 2), ... from page_densities;`. However, I doubt that this is faster ... — Trinimon, May 12 '15 at 12:19
How many columns,indexes on the table? What's the total data size? There aren't much room to improve if you have to update the who big table. If you need do it frequently, you may want to split the column to a new skinny table. — Tim3880, May 12 '15 at 12:31
Not sure if the same applies to PostgreSQL too, but in Oracle, this query runs significantly faster if you issue `LOCK TABLE page_densities IN EXCLUSIVE MODE` before 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 — Erich Kitzmueller, May 12 '15 at 12:35
as this is only one time query. so however i have managed it by just setting `work_mem` but still wondering isn't there any solution ? thanks all by the way\ — Abdul Baig, May 12 '15 at 12:59

score 1 · Answer 1 · edited May 23 '17 at 12:07

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)

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

1 Answers1