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

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.


> exec("delete from foo where name = 'xx'");
> exec("insert into foo(name, bar) values('xx','blahblah'");


Both commands should generate B-Tree structure modification.

I expect that first variant is better, but It should depend on many 
others things - for examples triggers, other indexes ...


REPLACE/UPSERT command solves this problem, but It is still in the TODO 
list.
Zdenek