140 reputation
15
bio website arencambre.com
location Dallas, TX
age
visits member for 2 years, 8 months
seen Dec 11 at 14:17

Dec
11
accepted Slow Postgres updates if OTHER columns are indexed?
Dec
11
awarded  Editor
Dec
11
revised Slow Postgres updates if OTHER columns are indexed?
edited title
Dec
10
asked Slow Postgres updates if OTHER columns are indexed?
Dec
9
comment Why did Postgres UPDATE take 39 hours?
I am switching the best answer to you. Since I posted this, I have encountered other situations where indexes are the problem, even if the column being updated already has a value and has no index (!). It seems that Postgres has a problem with how it manages indexes on other columns. There's no reason for these other indexes to balloon a update's query time when the only alteration to a table is to update an unindexed column and you're not increasing the allocated space for any row of that column.
Dec
9
accepted Why did Postgres UPDATE take 39 hours?
Dec
4
comment Why did Postgres UPDATE take 39 hours?
Thanks. In this case, it's a boolean column that had an entry into every row already. I was changing the entry on some rows. I just confirmed: the update took only 2 hours after dropping all indexes. Beforehand, I had to stop the update after 18 hours because it was just taking too long. This is despite the fact that the column that was being updated definitely was not indexed.
Dec
3
comment Why did Postgres UPDATE take 39 hours?
2.5 years later I am doing something similar but on a larger table. Just to make sure, is it a good idea to drop all indexes, even if the single column I am updating is not indexed?
Nov
27
awarded  Supporter
Jun
16
awarded  Notable Question
Dec
3
awarded  Popular Question
Mar
28
awarded  Student
Mar
28
awarded  Scholar
Mar
28
asked Why did Postgres UPDATE take 39 hours?