0

I run a simple update query on a table:

UPDATE table_x SET col_a = (col_b AT TIME ZONE 'UTC') AT TIME ZONE col_tz;

When table_x consists of 100 entries, the query takes some milliseconds.
-> 100'000 entries = 2 seconds.
-> 1'000'000 entries = 20 seconds.
So far so good. But when the table consists of 20M entries, the query runs forever! Does anybody know the reason for this? And what can be done to overcome this?

Maybe I should add that I have only around 13 GB free disk space on my computer.

David Hanimann
  • 3
  • 2
  • It may happen in transaction: all updates must completed or declined. So you can execute your statement without transaction or in smaller subsets. – Justinas Jakavonis Jul 04 '17 at 08:25
  • I belive you should not update so many records inside one transaction. I am using Firebird and it behave the same way. When I need to write to database (UPDATE, INSERT, DELETE) I take care not to change more than 50000 records, but excact number depends on data which is changed. Basically, you should never change 1M records inside one transaction. – Daniel Vidić Jul 04 '17 at 08:29
  • 1
    Could you check if you are swapping in the 20M operation but not in the 1M one? – Fabian Pijcke Jul 04 '17 at 08:33
  • 1
    As far as I can see, there is no swap used in neither of the operations. – David Hanimann Jul 04 '17 at 08:42

1 Answers1

1

It is the normal behaviour, that update performance degrades. The problem is, that the index needs to be updated after every update. If you import data into the database, you should use a batch feature of your database, which disables the index temporarily and rebuilds it afterwards.

Same for Sqlite for example: Sqlite appending data performance linear degradation, is this solvable?

ceving
  • 21,900
  • 13
  • 104
  • 178