pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Ivar Zarans <iff@alcaron.ee>
To: pgsql-performance@postgresql.org
Subject: Re: Slow UPADTE, compared to INSERT
Date: Thu, 4 Dec 2003 22:45:21 +0000
Message-ID: <200312042245.21899.dev@archonet.com> (raw)
In-Reply-To: <20031204221312.GA18874@alcaron.ee>
References: <20031204185751.GA15893@alcaron.ee>
	<200312042023.37050.dev@archonet.com>
	<20031204221312.GA18874@alcaron.ee>

On Thursday 04 December 2003 22:13, Ivar Zarans wrote:
> 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!

Be aware, this is specific to PG - I'm not aware of this construction working 
on any other DB. Three minutes still doesn't sound brilliant, but that could 
be tuning issues.

> > 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.

Yep - the explain looked fine. If you run EXPLAIN ANALYSE it will give you 
timings too (actual timings will be slightly less than reported ones since PG 
won't be timing/reporting).

> 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?

Hmm - you didn't mention cursors. If this was a problem with PyPgSQL in 
general I suspect we'd know about it by now. It could however be some 
cursor-related issue. In general, you're probably better off trying to do 
updates/inserts as a single statement and letting PG manage things rather 
than processing one row at a time.

If you've got the time, try putting together a small test-script with some 
dummy data and see if it's reproducible. I'm sure the other Python users 
would be interested in seeing where the problem is.

-- 
  Richard Huxton
  Archonet Ltd



view thread (24+ messages)  latest in thread

Message-ID: <200312042245.21899.dev@archonet.com>
Permalink:  ../200312042245.21899.dev@archonet.com/
Also on:    postgresql.org/message-id/200312042245.21899.dev@archonet.com

 · 

reply

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: dev@archonet.com, iff@alcaron.ee
  Subject: Re: Slow UPADTE, compared to INSERT
  In-Reply-To: <200312042245.21899.dev@archonet.com>

* 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