X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6EBA7D1B4A7 for ; Thu, 4 Dec 2003 22:13:42 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 35109-02 for ; Thu, 4 Dec 2003 18:13:13 -0400 (AST) Received: from uhh.alcaron.ee (uhh.alcaron.ee [194.204.62.169]) by svr1.postgresql.org (Postfix) with ESMTP id 8D7CDD1B443 for ; Thu, 4 Dec 2003 18:13:10 -0400 (AST) Received: from iff by uhh.alcaron.ee with local id 1AS1ii-0004xy-00; Fri, 05 Dec 2003 00:13:12 +0200 Date: Fri, 5 Dec 2003 00:13:12 +0200 To: pgsql-performance@postgresql.org Subject: Re: Slow UPADTE, compared to INSERT Message-ID: <20031204221312.GA18874@alcaron.ee> References: <20031204185751.GA15893@alcaron.ee> <20031204142320.3c021100.threshar@torgo.978.org> <20031204195121.GA16497@alcaron.ee> <200312042023.37050.dev@archonet.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200312042023.37050.dev@archonet.com> User-Agent: Mutt/1.3.28i From: Ivar Zarans X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/105 X-Sequence-Number: 4965 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