Thread: Performance Question
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?
Just curious.
TIA
master=# select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)
Terry Lee Tucker <terry@esc1.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? UPDATE will probably be somewhat faster because it's only one SQL statement to parse, plan and execute. -Doug
Terry Lee Tucker <terry@esc1.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
On Wednesday 14 June 2006 13:24, Greg Stark <gsstark@mit.edu> wrote: > 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. This is definitely the fastest way to update tens of thousands of rows if you know they all need to be replaced. It saves on index lookups and also network latency to the feeding app. I have also had measurable success COPYing data into a temp table and then using joins against that to delete,update,or insert only the rows that actually need to be processed in the real table (saving unnecessary index updates). -- In a truly free society, "Alcohol, Tobacco and Firearms" would be a convenience store chain.
On Wednesday 14 June 2006 03:57 pm, Terry Lee Tucker <terry@esc1.com> thus communicated: --> 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? --> --> Just curious. --> --> TIA Thanks for the answers. This list is a BIG help to us all :o]
In article <200606141557.37321.terry@esc1.com>, Terry Lee Tucker <terry@esc1.com> wrote: % 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 have foreign key relationships to the table being updated, then deleting from that table will often be slower than updating. -- Patrick TJ McPhee North York Canada ptjm@interlog.com