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 3E913D1B441 for ; Thu, 4 Dec 2003 18:58:22 +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 93647-03 for ; Thu, 4 Dec 2003 14:57:52 -0400 (AST) Received: from uhh.alcaron.ee (uhh.alcaron.ee [194.204.62.169]) by svr1.postgresql.org (Postfix) with ESMTP id 43CD0D1B433 for ; Thu, 4 Dec 2003 14:57:51 -0400 (AST) Received: from iff by uhh.alcaron.ee with local id 1ARyff-00048V-00; Thu, 04 Dec 2003 20:57:51 +0200 Date: Thu, 4 Dec 2003 20:57:51 +0200 To: pgsql-performance@postgresql.org Subject: Slow UPADTE, compared to INSERT Message-ID: <20031204185751.GA15893@alcaron.ee> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.3.28i From: Ivar Zarans X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/84 X-Sequence-Number: 4944 Hello! I am relative newcomer to SQL and PostgreSQL world, so please forgive me if this question is stupid. I am experiencing strange behaviour, where simple UPDATE of one field is very slow, compared to INSERT into table with multiple indexes. I have two tables - one with raw data records (about 24000), where one field contains status information (varchar(10)). First table has no indexes, only primary key (recid). Second table contains processed records - some fields are same as first table, others are calculated during processing. Records are processed by Python script, which uses PyPgSQL for PostgreSQL access. Processing is done by selecting all records from table1 where status matches certain criteria (import). Each record is processed and results are inserted into table2, after inserting status field on same record in 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. 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) This way i got processing time of 24000 records down to about 16 minutes. About 13 minutes from this took last UPDATE statement. 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? I am using PostgreSQL 7.3.4, Debian/GNU Linux 3.0 (Woody), kernel 2.4.21, Python 2.3.2, PyPgSQL 2.4 -- Ivar Zarans