1

Say I have a table with some columns:

CREATE TABLE t
(
    key int PRIMARY KEY,
    c1  int,
    c2  text,
    c3  timestamptz
);

I now fetch a row from the table:

SELECT * FROM t WHERE id = 180;

In my application, I now only change the value of one column, let's say c2. Is it harmful performance-wise to do:

UPDATE t
SET    t1 = <exact the same value as before>,
       t2 = <new value>,
       t3 = <exact the same value as before>
WHERE  key = 180;

instead of:

UPDATE t
SET    t2 = <new value>
WHERE  key = 180;

?

3

Assuming network overhead can be ignored (e.g. you're not updating megabytes of text with the same values), it should not affect performance. However, if you have triggers for UPDATE with column lists, they will behave differently, depending on whether the column is mentioned as a target of UPDATE. For instance,

create  trigger test_au after update of t1 on t
    for each row 
    execute procedure after_update_t1();

update t set t2=<new value> where key = 180; -- trigger does not fire
update t set t1=<exact the same value as before>,
 t2=<new value> where key = 180; -- trigger does fire 

I also tested for HOT updates - no difference in Postgres 10.5. If the value of the indexed column doesn't change, it does a heap only update, at least according to pg_stat_user_tables - the same as if the indexed column doesn't participate in the update at all.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.