Re: Performance Question

Terry Lee Tucker <terry(at)esc1(dot)com> writes:

> Hello List:
>
> I've been told that an update to a record is equivalent to a delete and insert
> operation. We have a utility written in Perl that brings into sync certain
> elements of 50 thousand records on 8 structurally identical databases. We
> threw together the script and decided to just delete the record and re-insert
> it with the data that was brought into sync. Now the question: Is it just as
> fast to do it this way, or is there some hidden advantage to performing an
> update?

If you're doing the whole DELETE/INSERT as a single transaction then it should
be roughly comparable. The UPDATE operation tries to keep the records on the
same page which makes it a faster operation all else being equal, but all else
is rarely equal.

One way it would be unequal is if you can do your DELETE as a single query and
the insert operation as using a single large COPY FROM. Even if you issue 50
thousand INSERTs and a single big DELETE that would be better than issuing 50
thousand separate UPDATEs that have to use index lookups to track down the
tuples being updated.

Just be sure not to be issuing 50 thousand separate transactions, that will be
*much* slower.

--
greg