CODEWITHSUNDEEP
postgresqlindexingsql-update
Giles
Giles

Reputation: 1667

PostgreSQL slow update with index

Very simply update to reset 1 column in a table with approx 5mil rows as:

UPDATE t_Daily 
SET Price= NULL

Price is not part of any of the indexes on that table.

Running this without indexes takes 45s.

Running this with one or more indexes takes at least 20 mins (I keep having to stop it).

I fully understand why maintaining indexes affects the performance of insert and update statements, but this update makes no changes to the table indexes so why does it have this terrible effect on performance?

Any ideas much appreciated.

Upvotes: 4

Views: 4057

Answers (2)

Giles
Giles

Reputation: 1667

Found some further info (thanks to Laurenz-Albe for the HOT tip).

This link https://malisper.me/postgres-heap-only-tuples/ states that

Due to MVCC, an update in Postgres consists of finding the row being updated, and inserting a new version of the row back into the database. The main downside to doing this is the need to readd the row to every index

So it is re-writing the index despite only updating a column not in the index.

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 246383

That is normal and expected: updating an index can be about ten times as expensive as updating the table itself. The table has no ordering!

If price is not indexed, you can use HOT updates that avoid updating the indexes. To make use of that, the table has to be defined with a fillfactor under 100 so that updated rows can find room in the same block as the original rows.

Upvotes: 2

Related Questions