Re: Slow UPADTE, compared to INSERT

On Thu, Dec 04, 2003 at 02:23:20PM -0500, Jeff wrote:

> > Most interesting is, that insert takes 0.004 seconds in average, but
> > update takes 0.255 seconds in average. Processing of 24000 records
> > took around 1 hour 20 minutes.
>
> Do you have an index on recid?

Yes, this is primary key of table1

> and did you vacuum analyze after you loaded up the data?

No, this is running as nightly cronjob. All tests were done during one
day, so no vacuum was done.

> "IN" queries are terribly slow on versions before 7.4

OK, this is useful to know :)

> > Why is UPDATE so slow compared to INSERT? I would expect more or less
> > similar performance, or slower on insert since table2 has four indexes
> > in addition to primary key, table1 has only primary key, which is used
> > on update. Am i doing something wrong or is this normal?

> Remember, UPDATE has to do all the work of select and more.
>
> And if you have 4 indexes those will also add to the time (Since it has
> to update/add them to the tree)

My primary concern is performance difference between INSERT and UPDATE
in my first tests. There i did select from table1, fetched record,
processed it and inserted into table2. Then updated status of fetched
record in table1. Repeated in cycle as long as fetch returned record.
Average time for INSERT was 0.004 seconds, average time for UPDATE 0.255
seconds. Update was done as "update table1 set status = 'done' where
recid = xxxx". As far as i understand, this type of simple update should
be faster, compared to INSERT into table with four indexes, but in my
case it is more than 60 times slower. Why??

My second tests were done with temporary table and update query as:
"UPDATE table1 SET Status = 'done' WHERE recid IN (SELECT recid FROM
temptable)". It is still slower than INSERT, but more or less
acceptable. Compared to my first tests overall processing time dropped
from 1 hour and 20 minutes to 16 minutes.

So, my question remains - why is simple update more than 60 times
slower, compared to INSERT? Any ideas?

--
Ivar Zarans