From: Ivar Zarans <iff@alcaron.ee>
To: pgsql-performance@postgresql.org
Subject: Re: Slow UPADTE, compared to INSERT
Date: Fri, 5 Dec 2003 00:13:12 +0200
Message-ID: <20031204221312.GA18874@alcaron.ee> (raw)
In-Reply-To: <200312042023.37050.dev@archonet.com>
References: <20031204185751.GA15893@alcaron.ee>
<20031204142320.3c021100.threshar@torgo.978.org>
<20031204195121.GA16497@alcaron.ee>
<200312042023.37050.dev@archonet.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: iff@alcaron.ee
Subject: Re: Slow UPADTE, compared to INSERT
In-Reply-To: <20031204221312.GA18874@alcaron.ee>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox