Re: Is full-row updates slower than single-value updates

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> 2010/6/28 Bjrn Lindqvist <bjourne(at)gmail(dot)com>:
>> My question is like the subject, is it much slower to update all
>> columns values than just a single column? Generated update queries
>> from ORM:s generally have the following format:
>>
>> update foo set a = 1, b = 2, c = 3, .... where id = 1234;

> it depends. Pg create a new version of complete row for every update,
> so isn't important if you update one or all columns. But there are
> exception - TOAST columns. If you update any TOAST column, then UPDATE
> is significally slower, so - if you don't need to update these
> columns, then don't do it.

It's worth noting that updates like

update foo set a = a, b = b, c = something where ...

are pretty much equivalent in cost to

update foo set c = something where ...

ie, explicitly assigning a column its old value doesn't add anything to
the cost, not even for toasted columns. (In fact, the planner inserts
such assignments if you didn't request them, because that's necessary in
order to form the complete new tuple value.) I'm not sure if that's
true in other DBMSes but it's true in PG.

But assigning a new value to a column costs something, even if it
happens to be equal to the previous value. The cost is mainly in
parsing and converting the supplied value, and that's something that
every DBMS is going to be paying regardless of any optimizations it
might have later. I hope your ORM is not really stupid enough to do
explicit assignments to columns it knows already have that value ---
if it is, you need a less stupid ORM.

regards, tom lane