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 6921BD1B49A for ; Thu, 4 Dec 2003 20:47:07 +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 19863-01 for ; Thu, 4 Dec 2003 16:46:37 -0400 (AST) Received: from uhh.alcaron.ee (uhh.alcaron.ee [194.204.62.169]) by svr1.postgresql.org (Postfix) with ESMTP id 8211FD1C9ED for ; Thu, 4 Dec 2003 16:43:25 -0400 (AST) Received: from iff by uhh.alcaron.ee with local id 1AS0Jq-0004Y1-00; Thu, 04 Dec 2003 22:43:26 +0200 Date: Thu, 4 Dec 2003 22:43:26 +0200 To: pgsql-performance@postgresql.org Subject: Re: Slow UPADTE, compared to INSERT Message-ID: <20031204204326.GA17083@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/94 X-Sequence-Number: 4954 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; Thanks for the hint. I'll try this. > 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? Explain shows following output: explain update table1 set status = 'PROC' where recid = '199901'; Index Scan using table1_pkey on table1 (cost=0.00..6.01 rows=1 width=198) Index Cond: (recid = 199901::bigint) (2 rows) -- Ivar Zarans