On Tue, Jul 04, 2006 at 11:59:27AM +0200, Zdenek Kotala wrote: > Mark, > I don't know how it will exactly works in postgres but my expectations are: > > Mark Woodward wrote: > >Is there a difference in PostgreSQL performance between these two > >different strategies: > > > > > >if(!exec("update foo set bar='blahblah' where name = 'xx'")) > > exec("insert into foo(name, bar) values('xx','blahblah'"); > >or > > The update code generates new tuple in the datafile and pointer has been > changed in the indexfile to the new version of tuple. This action does > not generate B-Tree structure changes. If update falls than insert > command creates new tuple in the datafile and it adds new item into > B-Tree. It should be generate B-Tree node split.
Actually, not true. Both versions will generate a row row and create a new index tuple. The only difference may be that in the update case the may be a ctid link from the old version to the new one, but that's about it... Which is faster will probably depends on what is more common in your DB: row already exists or not. If you know that 99% of the time the row will exist, the update will probably be faster because you'll only execute one query 99% of the time. Have a nice day, -- Martijn van Oosterhout <firstname.lastname@example.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate.
Description: Digital signature