Re: update/insert,

Mark Woodward wrote:

>>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.
>>    
>>
>
>OK, but the point of the question is that constantly updating a single row
>steadily degrades performance, would delete/insery also do the same?
>
>  
>


If that was the point of the question, you should have said so.

And unless I am much mistaken the answer is "of course it will."

cheers

andrew