Thread: Updates: all or partial records
Scenario: You have to update a record. One or more fields are unchanged from the original record being altered. So you have two options: 1) Include those fields in your UPDATE statement, even though they are unchanged; 2) Omit unchanged fields from the UPDATE statement. My first inclination is to omit unchanged fields. However, I have the idea that PG simply marks the existing record to be dropped, and generates a whole new row by copying unspecified fields from the original record. My question is, which is more efficient? Performance-wise, does it matter whether unchanged fields are included or omitted on UPDATE statements? Paul -- Paul M. Foster
Paul M Foster wrote: > Scenario: You have to update a record. One or more fields are unchanged > from the original record being altered. So you have two options: 1) > Include those fields in your UPDATE statement, even though they are > unchanged; 2) Omit unchanged fields from the UPDATE statement. > > My first inclination is to omit unchanged fields. However, I have the > idea that PG simply marks the existing record to be dropped, and > generates a whole new row by copying unspecified fields from the > original record. > > 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.
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. cheers -- vbi -- Protect your privacy - encrypt your email: http://fortytwo.ch/gpg/intro
Attachment
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