Reputation
Top tag
Next privilege 200 Rep.
See reduced ads
Badges
1 5
Newest
 Editor
Impact
~7k people reached

  • 0 posts edited
  • 0 helpful flags
  • 4 votes cast
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?