CODEWITHSUNDEEP
sqlpostgresql
danbst
danbst

Reputation: 3623

Postgresql UPDATE constrained column with same value

Is there perfomance impact (like redundant index lookups) when UPDATEing constrained (unique, FK) column with the same value?

Say, I've changed field1 of my POJO, but UPDATE is done for all fields, like

UPDATE pojo SET field1=?, field2=?, field3=?, ... WHERE ...

(field2 is defined as UNIQUE and field3 is FK)

Upvotes: 0

Views: 213

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324531

Yes, there are. PostgreSQL tries to optimize them out in some places, but support is a bit limited.

For one thing, it can cause weird effects with locking and foreign keys.

For another, it'll prevent HOT updates of the row, forcing index updates where they might not otherwise be necessary.

While not generally an issue for unique key fields, you'll also force a re-write of out-of-line TOAST data if you update a TOASTed field to the same value, rather than leaving it out of the update entirely. This wastes space and disk I/O.

All in all, avoid such an update if you can. Update only fields you have actually changed.

Upvotes: 3

Related Questions