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