Re: [HACKERS] update/insert, delete/insert efficiency WRT vacuum
Ühel kenal päeval, T, 2006-07-04 kell 14:53, kirjutas Zeugswetter
Andreas DCP SD:
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
In pg, this strategy is generally more efficient, since a pk failing
insert would create
a tx abort and a heap tuple. (so in pg, I would choose the insert first
strategy only when
the insert succeeds most of the time (say 95%))
Note however that the above error handling is not enough, because two
different sessions
can still both end up trying the insert (This is true for all db systems
when using this strategy).
I think the recommended strategy is to first try tu UPDATE, if not found
then INSERT, if primary key violation on insert, then UPDATE
--
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia
Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] 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
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] 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.
signature.asc
Description: Digital signature
Re: [HACKERS] update/insert, delete/insert efficiency WRT vacuum and
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
In pg, this strategy is generally more efficient, since a pk failing
insert would create
a tx abort and a heap tuple. (so in pg, I would choose the insert first
strategy only when
the insert succeeds most of the time (say 95%))
Note however that the above error handling is not enough, because two
different sessions
can still both end up trying the insert (This is true for all db systems
when using this strategy).
Andreas
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[HACKERS] update/insert, delete/insert efficiency WRT vacuum and MVCC
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
exec(delete from foo where name = 'xx');
exec(insert into foo(name, bar) values('xx','blahblah');
In my session handler code I can do either, but am curious if it makes any
difference. Yes, name is unique.
---(end of broadcast)---
TIP 6: explain analyze is your friend
