Thread: Is full-row updates slower than single-value updates
Hello everyone, 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; So each column is touched which, I suspect, is much slower than just updating a single column because more indexes has to be rebuilt and so on. EXPLAIN ANALYZE does not give any hints and claims that both queries are equally fast. Maybe someone can guide me to documentation which explains how to estimate how costly row-level updates are? -- mvh Björn
Hello 2010/6/28 Björn Lindqvist <bjourne@gmail.com>: > Hello everyone, > > 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; > > So each column is touched which, I suspect, is much slower than just > updating a single column because more indexes has to be rebuilt and so > on. EXPLAIN ANALYZE does not give any hints and claims that both > queries are equally fast. Maybe someone can guide me to documentation > which explains how to estimate how costly row-level updates are? > 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. Regards Pavel > -- > mvh Björn > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2010/6/28 Björn Lindqvist <bjourne@gmail.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
Den 28 juni 2010 20.22 skrev Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> 2010/6/28 Björn Lindqvist <bjourne@gmail.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
=?ISO-8859-1?Q?Bj=F6rn_Lindqvist?= <bjourne@gmail.com> writes:
> Den 28 juni 2010 20.22 skrev Tom Lane <tgl@sss.pgh.pa.us>:
>> But assigning a new value to a column costs something, even if it
>> happens to be equal to the previous value.
> But do I really need to worry about the cost of casting strings to
> ints and other such parsing operations?
Integers? Maybe not. Timestamps? Probably you ought to think about
it. Strings long enough to need toasting? Definitely.
> 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?
It shouldn't ordinarily, but if the data is long enough to get toasted
then updates are going to happen anyway, both in the toast table and
the main table.
The long and the short of it here is that being lazy is going to cost
you eventually. Maybe it's all right in a single small application
where you can be confident about what sort of data will be stored, but
an allegedly general-purpose ORM should *not* be making that sort of
assumption.
regards, tom lane