4

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?

d3cima
  • 729
  • 1
  • 10
  • 31
  • 1
    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 –  Aug 23 '18 at 14:57
  • I have this kind of queries that runs in the same time : `SHOW TRANSACTION ISOLATION LEVEL` – d3cima Aug 23 '18 at 14:58
  • 3
    BTW: if you don't add a semicolon to the query it might take forever (assuming the psql front end) – joop Aug 23 '18 at 14:59
  • @joop I use datagrip and no need of semicolon to start a request :-) – d3cima Aug 23 '18 at 15:14

1 Answers1

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/

Sam Griffiths
  • 56
  • 1
  • 3