Re: update/insert, delete/insert efficiency WRT vacuum and

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   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.