Re: Slow UPADTE, compared to INSERT

On Thu, Dec 04, 2003 at 08:23:36PM +0000, Richard Huxton wrote:

> Ah - it's probably not the update but the IN. You can rewrite it using PG's
> non-standard FROM:
>
> UPDATE t1 SET status='done' FROM t_tmp WHERE t1.rec_id = t_tmp.rec_id;

This was one *very useful* hint! Using this method i got my processing
time of 24000 records down to around 3 minutes 10 seconds. Comparing
with initial 1 hour 20 minutes and then 16 minutes, this is impressive
improvement!

> Now that doesn't explain why the update is taking so long. One fifth of a
> second is extremely slow. Are you certain that the index is being used?

I posted results of "EXPLAIN" in my previous message. Meanwhile i tried
to update just one record, using "psql". Also tried out "EXPLAIN
ANALYZE". This way i did not see any big delay - total runtime for one
update was around 1 msec.

I am confused - has slowness of UPDATE something to do with Python and
PyPgSQL, since "psql" seems to have no delay whatsoever? Or is this
related to using two cursors, one for select results and other for
update? Even if this is related to Python or cursors, how am i getting
so big speed improvement only by using different query?

--
Ivar