On 25/01/2010 5:29 PM, Adrian von Bidder wrote:
On Monday 25 January 2010 08.25:30 John R Pierce wrote:
My question is, which is more efficient? Performance-wise, does it
matter whether unchanged fields are included or omitted on UPDATE
statements
my first order guess is, sending and having to parse the additional
unchanged fields in your UPDATE statement is more expensive than letting
the engine just copy them from the old tuple to the new.
Especially since setting unchanged fields might also trigger all sorts of
unneeded DB activity (check constraints, and doesn't pg now also allow
firing trigger based on which fields were updated?) which will (presumably,
don't know the code and haven't tested it) will probably not be triggered if
postgres can know that the value is not to be changed.
It's also a whole lot clearer for anyone reading the logs with statement
logging enabled, makes it clearer what the actual intent of the UPDATE
statement is, etc.
In addition, if you decide to start using column permissions later
you'll need to omit columns you don't have UPDATE permission on for the
current user/role, and the best way to do that is never update columns
you haven't actually changed.
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
