Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 92B63650EF8
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 25 Jun 2008 07:11:16 -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 62084-09 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 25 Jun 2008 07:11:03 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail01.enterprisedb.com (mail01.enterprisedb.com
 [63.246.7.168])
	by postgresql.org (Postfix) with ESMTP id 16AEB650E82
	for <pgsql-performance@postgresql.org>;
	Wed, 25 Jun 2008 07:11:06 -0300 (ADT)
thread-index: AcjWq+QPgeRl80N/Sa+W/On5mgp+zw==
Content-Class: urn:content-classes:message
Received: from [80.222.70.18] ([80.222.70.18]) by mail01.enterprisedb.com over
	TLS secured channel with Microsoft SMTPSVC(6.0.3790.3959);
	Wed, 25 Jun 2008 06:11:52 -0400
Importance: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4133
Message-ID: <486219B6.7050003@enterprisedb.com>
Date: Wed, 25 Jun 2008 13:11:02 +0300
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
Organization: EnterpriseDB
User-Agent: Mozilla-Thunderbird 2.0.0.14 (X11/20080509)
MIME-Version: 1.0
To: "jay" <jackem.mojx@alibaba-inc.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Postgresql update op is very very slow
References: <473587.97248.qm@web32701.mail.mud.yahoo.com>
	<op.uc83c7m2cigqcu@apollo13.peufeu.com>
	<004b01c8d671$3dec1630$0544000a@hz.ali.com>
In-Reply-To: <004b01c8d671$3dec1630$0544000a@hz.ali.com>
Content-Type: text/plain;
	format=flowed;
	charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit
X-OriginalArrivalTime: 25 Jun 2008 10:11:52.0906 (UTC)
	FILETIME=[E3C3A2A0:01C8D6AB]
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200806/124
X-Sequence-Number: 30474

jay wrote:
> 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? 

Possibly. Because of MVCC, a full-table update will actually create a 
new version of each row.

I presume that's a one-off query, or a seldom-run batch operation, and 
not something your application needs to do often. In that case, you 
could drop all indexes, and recreate them after the update, which should 
help a lot:

BEGIN;
DROP INDEX <index name>, <index name 2>, ...; -- for each index
UPDATE msg_table SET type = 0;
CREATE INDEX ... -- Recreate indexes
COMMIT;

Or even better, instead of using UPDATE, do a SELECT INTO a new table, 
drop the old one, and rename the new one in its place. That has the 
advantage that the new table doesn't contain the old row version, so you 
don't need to vacuum right away to reclaim the space.

Actually, there's an even more clever trick to do roughly the same thing:

ALTER TABLE msg_table ALTER COLUMN type TYPE int4 USING 0;

(assuming type is int4, replace with the actual data type if necessary)

This will rewrite the table, similar to a DROP + CREATE, and rebuild all 
indexes. But all in one command.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com