Certain updates take far too long on large Postgres tables. Given these conditions:
- only one column is being updated, and it is non-indexed
- the column already has data in it for every row due to a prior update
- the data is not changing size (e.g., rewriting a boolean value)
- there is no other column in this or any other table that depends on the value of the column being updates
- there are no other queries being performed on the database (this is a personal research database on a workstation, not an enterprise database)
- there are indexes on other columns
- spinning drive (not SSD) with Bitlocker and fast PC with Windows 8.1 x64
- the table has 10 million rows and 60 columns
...you'd think that the update would take a reasonable amount of time relative to expectations of spinning media with Bitlocker. We're not creating more data, so existing data shouldn't need to be moved around on the HDD, it just needs to be overwritten. The other indexes shouldn't need changes. Etc. Instead, after 20 hours of constant HDD grinding, I get tired of waiting and stop the query. If I drop all indexes on other columns and re-run the query, it takes only about 30 minutes.
Why do indexes on columns irrelevant to this query balloon the update time?