Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 910BD9FC0B1 for ; Thu, 22 Feb 2007 14:19:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 34436-01-3 for ; Thu, 22 Feb 2007 14:18:37 -0400 (AST) X-Greylist: delayed 00:07:00.553159 by SQLgrey-1.7.4 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.173]) by postgresql.org (Postfix) with ESMTP id BFACB9FBF39 for ; Thu, 22 Feb 2007 14:18:43 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id k3so161725ugf for ; Thu, 22 Feb 2007 10:18:43 -0800 (PST) Received: by 10.67.22.7 with SMTP id z7mr992964ugi.1172167898912; Thu, 22 Feb 2007 10:11:38 -0800 (PST) Received: from ?192.168.0.77? ( [193.251.48.28]) by mx.google.com with ESMTP id w5sm3633377mue.2007.02.22.10.11.37; Thu, 22 Feb 2007 10:11:37 -0800 (PST) Message-ID: <45DDDCDE.9050806@beemotechnologie.com> Date: Thu, 22 Feb 2007 19:11:42 +0100 Reply-To: gabriel.biberian@beemotechnologie.com User-Agent: Thunderbird 2.0b2 (X11/20070116) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: slow update on 1M rows (worse with indexes) Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit From: Gabriel Biberian X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.069 tagged_above=0 required=5 tests=AWL, BAYES_50 X-Spam-Level: X-Archive-Number: 200702/230 X-Sequence-Number: 23284 Hello, I experience significant performance issues with postgresql and updates. I have a table which contains ~1M rows. Layout: TOTO=# \d versions_9d; Table «public.versions_9d» Colonne | Type | Modificateurs ------------+------------------------+--------------- hash | character(32) | date | integer | default 0 diff | integer | default 0 flag | integer | default 0 size | bigint | default 0 zip_size | bigint | default 0 jds | integer | default 0 scanned | integer | default 0 dead | integer | default 0 Test case: Create a new DB and load a dump of the above database with 976009 rows, then i perform updates on the whole table. I recorded the time taken for each full update and the amount of extra disk space used. Each consecutive update of the table is slower than the previous beebox@evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=2" UPDATE 976009 real 0m41.542s beebox@evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=3" UPDATE 976009 real 0m45.140s (+480M) beebox@evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=4" UPDATE 976009 real 1m10.554s (+240M) beebox@evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=5" UPDATE 976009 real 1m24.065s (+127M) beebox@evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=6" UPDATE 976009 real 1m17.758s (+288M) beebox@evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=7" UPDATE 976009 real 1m26.777s (+288M) beebox@evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=8" UPDATE 976009 real 1m39.151s (+289M) Then i tried adding an index to the table on the column date (int) that stores unix timestamps. TOTO=# CREATE INDEX versions_index ON versions_9d (date); (-60M) disk space goes down on index creation beebox@evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=9" UPDATE 976009 real 3m8.219s (+328M) beebox@evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=8" UPDATE 976009 real 6m24.716s (+326M) beebox@evobrik01:~$ time psql TOTO -c "UPDATE versions_9d SET flag=10" UPDATE 976009 real 8m25.274s (+321M) As a sanity check, i loaded mysql5 and tried the same database and updates. With mysql, the update always lasts ~8s. The conclusions I have come to is that update==insert+delete which seems very heavy when index are present (and heavy disk wise on big tables). Is there a switch i can flip to optimise this? Thanks in advance, Gabriel Biberian