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

Den 28 juni 2010 20.22 skrev Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2010/6/28 Björn 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.

But do I really need to worry about the cost of casting strings to
ints and other such parsing operations? Seem to me that they probably
ought to be fast enough. So I cant really make sense of your replies.
No column in the table is a toast column, although there are a few
string columns with quite long urls. If they are indexed, does
updating them with the same data trigger index updates? Or maybe
postgres is smart enough to not rebuild the indexes.

--
mvh Björn