Slow update on update query Postgresql

Question

I run the an update query on a table of 36 millions of lines. This request takes 45 minutes to run. processed field is indexed, and the database is on a ssd.

UPDATE batch_item SET processed=true

do you have a clue why this is so long?

This updates all rows in the table - which will take some while. However updating 36 million rows shouldn't take that long. My guess is that it was waiting for some row locks during that time because other transactions were also updating some rows — user330315
– user330315, Commented Aug 23, 2018 at 14:57
I have this kind of queries that runs in the same time : SHOW TRANSACTION ISOLATION LEVEL — d3cima
– d3cima, Commented Aug 23, 2018 at 14:58
BTW: if you don't add a semicolon to the query it might take forever (assuming the psql front end) — joop
– joop, Commented Aug 23, 2018 at 14:59
@joop I use datagrip and no need of semicolon to start a request :-) — d3cima
– d3cima, Commented Aug 23, 2018 at 15:14

Sam Griffiths · Accepted Answer · 2018-08-23 15:22:24Z

2

I don't know how important your index is and whether 100% availability of it is crucial, but dropping the index, setting the value and adding the index back on at the end may save you time.

There's some useful information on bulk update operations here: https://www.codacy.com/blog/how-to-update-large-tables-in-postgresql/

answered Aug 23, 2018 at 15:22

Sam Griffiths

561 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Douglas Gaskell Over a year ago

This is a dead link. The information from this link should be included in the answer to avoid link rot.

Collectives™ on Stack Overflow

Slow update on update query Postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related