Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id CD400650E97
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 26 Jun 2008 07:06:10 -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 83166-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 26 Jun 2008 07:06:00 -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 D00E3650E82
	for <pgsql-performance@postgresql.org>;
	Thu, 26 Jun 2008 07:06:02 -0300 (ADT)
X-IronPort-AV: E=Sophos;i="4.27,708,1204473600"; d="scan'208";a="70015844"
From: "jay" <jackem.mojx@alibaba-inc.com>
To: "'Heikki Linnakangas'" <heikki@enterprisedb.com>
Cc: <pgsql-performance@postgresql.org>
References: <473587.97248.qm@web32701.mail.mud.yahoo.com>
	<op.uc83c7m2cigqcu@apollo13.peufeu.com>
	<004b01c8d671$3dec1630$0544000a@hz.ali.com>
	<486219B6.7050003@enterprisedb.com>
Subject: =?gb2312?B?tPC4tDogW1BFUkZPUk1dIFBvc3RncmVzcWwgdXBkYXRlIG9wIGlzIA==?=
	=?gb2312?B?dmVyeSB2ZXJ5IHNsb3c=?=
Date: Thu, 26 Jun 2008 18:04:18 +0800
Message-ID: <005101c8d773$ff81b2c0$0644000a@hz.ali.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="gb2312"
Content-Transfer-Encoding: quoted-printable
X-Mailer: Microsoft Office Outlook 11
Thread-Index: AcjWq+QPgeRl80N/Sa+W/On5mgp+zwAld2dQ
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
In-Reply-To: <486219B6.7050003@enterprisedb.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.152 tagged_above=0 required=5 tests=AWL=0.152
X-Spam-Level: 
X-Archive-Number: 200806/161
X-Sequence-Number: 30511

	I know the problem, because there are about 35 million rows , which
cost about 12G disk space and checkpoint segments use 64, but update
operation is in one transaction which lead fast fill up the checkpoint
segments and lead do checkpoints frequently, but checkpoints will cost =
lots
resources, so update operation become slowly and slowly and bgwrite =
won't
write because it's not commit yet.
Create a new table maybe a quick solution, but it's not appropriated in =
some
cases.
	If we can do commit very 1000 row per round, it may resolve the
problem.
But  PG not support transaction within function yet?=20

-----=D3=CA=BC=FE=D4=AD=BC=FE-----
=B7=A2=BC=FE=C8=CB: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] =B4=FA=B1=ED Heikki =
Linnakangas
=B7=A2=CB=CD=CA=B1=BC=E4: 2008=C4=EA6=D4=C225=C8=D5 18:11
=CA=D5=BC=FE=C8=CB: jay
=B3=AD=CB=CD: pgsql-performance@postgresql.org
=D6=F7=CC=E2: Re: [PERFORM] Postgresql update op is very very slow

jay wrote:
> I've a table with about 34601755 rows ,when I execute 'update =
msg_table
set
> type=3D0;' is very very slow, cost several hours, but still not =
complete?
>=20
> Why postgresql is so slowly? Is the PG MVCC problem?=20

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

I presume that's a one-off query, or a seldom-run batch operation, and=20
not something your application needs to do often. In that case, you=20
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 =3D 0;
CREATE INDEX ... -- Recreate indexes
COMMIT;

Or even better, instead of using UPDATE, do a SELECT INTO a new table,=20
drop the old one, and rename the new one in its place. That has the=20
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.

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

--=20
Sent via pgsql-performance mailing list =
(pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance