DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=d0hYayJu6poAbyx4prKEIOxeIEv87ARBFI7kA2divkd/s0sP2uUawc36d3EzyNaulb
	zZCJeb5W8swgWqKZpuSu1q+7VyKSVisnUypOF0CDtcyYKmNzkzvNxpelEKDElBWLJ9QG
	A8DzyDieHv2UcAQGhS8ZiuA7UCrs7qK3Z3gCQ=
Message-ID: <dcc563d10806260902o581ec3afud773867c1d0d2c69@mail.gmail.com>
Date: Thu, 26 Jun 2008 10:02:31 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: =?GB2312?B?UmU6IFtQRVJGT1JNXSC08Li0OiBbUEVSRk9STV0gUG9zdGc=?=
	=?GB2312?B?cmVzcWwgdXBkYXRlIG9wIGlzIHZlcnkgdmVyeSBzbG93?=
Cc: jay <jackem.mojx@alibaba-inc.com>,
	"Heikki Linnakangas" <heikki@enterprisedb.com>,
	pgsql-performance@postgresql.org
In-Reply-To: <16213.1214492661@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <473587.97248.qm@web32701.mail.mud.yahoo.com>
	<op.uc83c7m2cigqcu@apollo13.peufeu.com>
	<004b01c8d671$3dec1630$0544000a@hz.ali.com>
	<486219B6.7050003@enterprisedb.com>
	<005101c8d773$ff81b2c0$0644000a@hz.ali.com>
	<16213.1214492661@sss.pgh.pa.us>

2008/6/26 Tom Lane <tgl@sss.pgh.pa.us>:
> "jay" <jackem.mojx@alibaba-inc.com> writes:
>>       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.
>
> No, that's utterly unrelated.  Transaction boundaries have nothing to do
> with checkpoints.

True.  But if you update 10000 rows and vacuum you can keep the bloat
to something reasonable.

On another note, I haven't seen anyone suggest adding the appropriate
where clause to keep from updating rows that already match.  Cheap
compared to updating the whole table even if a large chunk aren't a
match.  i.e.

... set col=0 where col <>0;

That should  be the first thing you reach for in this situation, if it can help.