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 DAE58D1B482 for ; Thu, 4 Dec 2003 19:24:10 +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 99873-02 for ; Thu, 4 Dec 2003 15:23:41 -0400 (AST) Received: from jefftrout.com (h00a0cc4084e5.ne.client2.attbi.com [24.128.241.68]) by svr1.postgresql.org (Postfix) with SMTP id 7515AD1B45B for ; Thu, 4 Dec 2003 15:23:39 -0400 (AST) Received: (qmail 26680 invoked from network); 4 Dec 2003 19:23:45 -0000 Received: from localhost (HELO squeegit) (threshar@127.0.0.1) by localhost with SMTP; 4 Dec 2003 19:23:45 -0000 Date: Thu, 4 Dec 2003 14:23:20 -0500 From: Jeff To: Ivar Zarans Cc: pgsql-performance@postgresql.org Subject: Re: Slow UPADTE, compared to INSERT Message-Id: <20031204142320.3c021100.threshar@torgo.978.org> In-Reply-To: <20031204185751.GA15893@alcaron.ee> References: <20031204185751.GA15893@alcaron.ee> X-Mailer: Sylpheed version 0.9.7 (GTK+ 1.2.10; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/87 X-Sequence-Number: 4947 On Thu, 4 Dec 2003 20:57:51 +0200 Ivar Zarans wrote: . > table1 is updated with new value (done). Update statement itself is > extremely simple: "update table1 set status = 'done' where recid = > ..." > > Most interesting is, that insert takes 0.004 seconds in average, but > update takes 0.255 seconds in average. Processing of 24000 records > took around 1 hour 20 minutes. Do you have an index on recid? and did you vacuum analyze after you loaded up the data? > > Then i changed processing logic not to update every record in table1 > after processing. Instead i did insert recid value into temporary > table and updated records in table1 after all records were processed > and inserted into table2: > UPDATE table1 SET Status = 'done' WHERE recid IN (SELECT recid FROM > temptable) > "IN" queries are terribly slow on versions before 7.4 > Why is UPDATE so slow compared to INSERT? I would expect more or less > similar performance, or slower on insert since table2 has four indexes > in addition to primary key, table1 has only primary key, which is used > on update. Am i doing something wrong or is this normal? > Remember, UPDATE has to do all the work of select and more. And if you have 4 indexes those will also add to the time (Since it has to update/add them to the tree) -- Jeff Trout http://www.jefftrout.com/ http://www.stuarthamm.net/