Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 43DB4650E82 for ; Wed, 25 Jun 2008 01:58:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 71244-04 for ; Wed, 25 Jun 2008 01:58:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp.alibaba-inc.com (smtp.alibaba-inc.com [121.0.17.65]) by postgresql.org (Postfix) with ESMTP id 58935650E80 for ; Wed, 25 Jun 2008 01:57:56 -0300 (ADT) X-IronPort-AV: E=Sophos;i="4.27,700,1204473600"; d="scan'208";a="69750569" From: "jay" To: References: <473587.97248.qm@web32701.mail.mud.yahoo.com> Subject: Postgresql update op is very very slow Date: Wed, 25 Jun 2008 11:12:03 +0800 Message-ID: <004b01c8d671$3dec1630$0544000a@hz.ali.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcjV9BdRDq8edyOyRlq4pExzVs2uHgAekTjw X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198 In-Reply-To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200806/118 X-Sequence-Number: 30468 I've a table with about 34601755 rows ,when I execute 'update msg_table set type=0;' is very very slow, cost several hours, but still not complete? Why postgresql is so slowly? Is the PG MVCC problem? But I try it on Mysql, the same table and rows, it only cost about 340 seconds. Any idea for the problem? My machine config: Memory 8G, 8 piece 15K disk , 2CPU(Quad-Core) AMD OS: Red Hat AS4 My postgres.conf main parameter is following: shared_buffers = 5GB # min 128kB or max_connections*16kB # (change requires restart) temp_buffers = 512MB # min 800kB work_mem = 400MB # min 64kB maintenance_work_mem = 600MB # min 1MB max_fsm_pages = 262144 # 2G min max_fsm_relations*16, 6 bytes each max_fsm_relations = 2000 # min 100, ~70 bytes each bgwriter_delay = 20ms # 10-10000ms between rounds bgwriter_lru_maxpages = 500 # 0-1000 max buffers written/round bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers