Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id A25BF633B2D
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  7 Jan 2010 12:38:40 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 79881-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Thu,  7 Jan 2010 16:38:27 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from mail-ew0-f226.google.com (mail-ew0-f226.google.com
	[209.85.219.226])
	by mail.postgresql.org (Postfix) with ESMTP id 81AC66335E5
	for <pgsql-performance@postgresql.org>;
	Thu,  7 Jan 2010 12:38:27 -0400 (AST)
Received: by ewy26 with SMTP id 26so16833436ewy.3
	for <pgsql-performance@postgresql.org>;
	Thu, 07 Jan 2010 08:38:26 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma;
	h=domainkey-signature:mime-version:received:in-reply-to:references
	:date:message-id:subject:from:to:cc:content-type;
	bh=vnJ6X/BI1GC530f9RSQk7V8yyy1AKGjwu+i0AzTrSBY=;
	b=S4pAP6wc5QFRWwE9ABSbmdTa927CjgW3MjtUzz0hU/YrSpJS1mkDhULfQV9KqMnhFm
	Sgv47tyNfvN6H2zBV6R4RcOSyX4XHG4chqcY0Dlobp+fGfx3y5shAVXUkSo5fxbeMgTn
	7Pc7frFahDR4uCnVfSGj+oeIR+Dm8VQh3UER0=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=tg0FhlZr22VLxO9zXig1uI4nDffJabB3jOcQn9ankpNVf5geEAkEXUjlsurPbhj3D/
	FeoMocxXAanCenQyYEu/2OXaaaxxAIm/f+EjLS/BenPsHfegWTpC6hfr/iIDHXWQxzsO
	yw08YGzftKLaW3+18lxJnkLzkppq4AmckgZhM=
MIME-Version: 1.0
Received: by 10.216.86.66 with SMTP id v44mr3159771wee.163.1262882306104;
 Thu,
	07 Jan 2010 08:38:26 -0800 (PST)
In-Reply-To: <4B4601FE.5010300@beecom.ch>
References: <hi41os$eiv$1@news.hub.org> <4B45DE4C.4050606@beecom.ch>
	<4B45A6FE020000250002DEBC@gw.wicourts.gov>
	<4B4601FE.5010300@beecom.ch>
Date: Thu, 7 Jan 2010 17:38:26 +0100
Message-ID: <2fe468a21001070838n40f1cbb5y19b34c5c4748a62d@mail.gmail.com>
Subject: Re: Massive table (500M rows) update nightmare
From: Ludwik Dylag <ldylag@gmail.com>
To: Leo Mannhart <leo.mannhart@beecom.ch>
Cc: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=0016e6d99fa13f9eb8047c95b368
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.668 tagged_above=-10 required=5 tests=AWL=-0.929,
	BAYES_20=-0.74, HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 201001/106
X-Sequence-Number: 36986

--0016e6d99fa13f9eb8047c95b368
Content-Type: text/plain; charset=ISO-8859-2
Content-Transfer-Encoding: quoted-printable

I would suggest:
1. turn off autovacuum
1a. ewentually tune db for better performace for this kind of operation
(cant not help here)
2. restart database
3. drop all indexes
4. update
5. vacuum full table
6. create indexes
7. turn on autovacuum

Ludwik


2010/1/7 Leo Mannhart <leo.mannhart@beecom.ch>

> Kevin Grittner wrote:
> > Leo Mannhart <leo.mannhart@beecom.ch> wrote:
> >
> >> You could also try to just update the whole table in one go, it is
> >> probably faster than you expect.
> >
> > That would, of course, bloat the table and indexes horribly.  One
> > advantage of the incremental approach is that there is a chance for
> > autovacuum or scheduled vacuums to make space available for re-use
> > by subsequent updates.
> >
> > -Kevin
> >
>
> ouch...
> thanks for correcting this.
> ... and forgive an old man coming from Oracle ;)
>
> Leo
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org=
)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>



--=20
Ludwik Dyl=B1g

--0016e6d99fa13f9eb8047c95b368
Content-Type: text/html; charset=ISO-8859-2
Content-Transfer-Encoding: quoted-printable

I would suggest:<div>1. turn off autovacuum</div><div>1a. ewentually tune d=
b for better performace for this kind of operation (cant not help here)</di=
v><div>2. restart database</div><div>3. drop all indexes</div><div>4. updat=
e</div>
<div>5. vacuum full table</div><div>6. create indexes</div><div>7. turn on =
autovacuum</div><div><br></div><div>Ludwik<br><br></div><div><br><div class=
=3D"gmail_quote">2010/1/7 Leo Mannhart <span dir=3D"ltr">&lt;<a href=3D"mai=
lto:leo.mannhart@beecom.ch">leo.mannhart@beecom.ch</a>&gt;</span><br>
<blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p=
x #ccc solid;padding-left:1ex;"><div class=3D"im">Kevin Grittner wrote:<br>
&gt; Leo Mannhart &lt;<a href=3D"mailto:leo.mannhart@beecom.ch">leo.mannhar=
t@beecom.ch</a>&gt; wrote:<br>
&gt;<br>
&gt;&gt; You could also try to just update the whole table in one go, it is=
<br>
&gt;&gt; probably faster than you expect.<br>
&gt;<br>
&gt; That would, of course, bloat the table and indexes horribly. =A0One<br=
>
&gt; advantage of the incremental approach is that there is a chance for<br=
>
&gt; autovacuum or scheduled vacuums to make space available for re-use<br>
&gt; by subsequent updates.<br>
&gt;<br>
&gt; -Kevin<br>
&gt;<br>
<br>
</div>ouch...<br>
thanks for correcting this.<br>
... and forgive an old man coming from Oracle ;)<br>
<font color=3D"#888888"><br>
Leo<br>
</font><div><div></div><div class=3D"h5"><br>
--<br>
Sent via pgsql-performance mailing list (<a href=3D"mailto:pgsql-performanc=
e@postgresql.org">pgsql-performance@postgresql.org</a>)<br>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-performance" target=3D"=
_blank">http://www.postgresql.org/mailpref/pgsql-performance</a><br>
</div></div></blockquote></div><br><br clear=3D"all"><br>-- <br>Ludwik Dyl=
=B1g<br>
</div>

--0016e6d99fa13f9eb8047c95b368--