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 70EC5D1B8F5 for ; Thu, 4 Dec 2003 19:55:49 +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 08063-03 for ; Thu, 4 Dec 2003 15:55:19 -0400 (AST) Received: from uhh.alcaron.ee (uhh.alcaron.ee [194.204.62.169]) by svr1.postgresql.org (Postfix) with ESMTP id 316C8D1B4A6 for ; Thu, 4 Dec 2003 15:51:21 -0400 (AST) Received: from iff by uhh.alcaron.ee with local id 1ARzVR-0004Ia-00; Thu, 04 Dec 2003 21:51:21 +0200 Date: Thu, 4 Dec 2003 21:51:21 +0200 To: pgsql-performance@postgresql.org Subject: Re: Slow UPADTE, compared to INSERT Message-ID: <20031204195121.GA16497@alcaron.ee> References: <20031204185751.GA15893@alcaron.ee> <20031204142320.3c021100.threshar@torgo.978.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20031204142320.3c021100.threshar@torgo.978.org> User-Agent: Mutt/1.3.28i From: Ivar Zarans X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/92 X-Sequence-Number: 4952 On Thu, Dec 04, 2003 at 02:23:20PM -0500, Jeff wrote: > > 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? Yes, this is primary key of table1 > and did you vacuum analyze after you loaded up the data? No, this is running as nightly cronjob. All tests were done during one day, so no vacuum was done. > "IN" queries are terribly slow on versions before 7.4 OK, this is useful to know :) > > 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) My primary concern is performance difference between INSERT and UPDATE in my first tests. There i did select from table1, fetched record, processed it and inserted into table2. Then updated status of fetched record in table1. Repeated in cycle as long as fetch returned record. Average time for INSERT was 0.004 seconds, average time for UPDATE 0.255 seconds. Update was done as "update table1 set status = 'done' where recid = xxxx". As far as i understand, this type of simple update should be faster, compared to INSERT into table with four indexes, but in my case it is more than 60 times slower. Why?? 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. So, my question remains - why is simple update more than 60 times slower, compared to INSERT? Any ideas? -- Ivar Zarans