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 280A2D1B44C for ; Thu, 4 Dec 2003 20:24:13 +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 14240-08 for ; Thu, 4 Dec 2003 16:23:42 -0400 (AST) Received: from anchor-post-31.mail.demon.net (anchor-post-31.mail.demon.net [194.217.242.89]) by svr1.postgresql.org (Postfix) with ESMTP id D73B4D1B445 for ; Thu, 4 Dec 2003 16:23:40 -0400 (AST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-31.mail.demon.net with esmtp (Exim 3.35 #1) id 1AS00j-0001qN-0V; Thu, 04 Dec 2003 20:23:41 +0000 Received: by mainbox.archonet.com (Postfix, from userid 529) id 3B0F517336; Thu, 4 Dec 2003 20:23:39 +0000 (GMT) Received: from client17.archonet.com (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id A787C16C60; Thu, 4 Dec 2003 20:23:37 +0000 (GMT) From: Richard Huxton To: Ivar Zarans , pgsql-performance@postgresql.org Subject: Re: Slow UPADTE, compared to INSERT Date: Thu, 4 Dec 2003 20:23:36 +0000 User-Agent: KMail/1.5 References: <20031204185751.GA15893@alcaron.ee> <20031204142320.3c021100.threshar@torgo.978.org> <20031204195121.GA16497@alcaron.ee> In-Reply-To: <20031204195121.GA16497@alcaron.ee> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200312042023.37050.dev@archonet.com> X-Bogosity: No, tests=bogofilter, spamicity=0.000000, version=0.15.3 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/97 X-Sequence-Number: 4957 On Thursday 04 December 2003 19:51, Ivar Zarans wrote: > > 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. 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; 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? -- Richard Huxton Archonet Ltd