X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id A07929FA304
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 11 Sep 2006 16:12:30 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 20592-03-7
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 11 Sep 2006 16:12:16 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from servata.com (ip-216-152-249-241.servata.com [216.152.249.241])
	by postgresql.org (Postfix) with ESMTP id 247D49FB3E2
	for <pgsql-performance@postgresql.org>;
	Mon, 11 Sep 2006 16:12:03 -0300 (ADT)
Received: from [209.162.219.253] (helo=dogma.v10.wvs)
	by servata.com with esmtpa (Exim 4.50)
	id 1GMrC7-0001jt-Qr; Mon, 11 Sep 2006 12:11:48 -0700
Subject: Re: Performance problem with Sarge compared with Woody
From: Jeff Davis <pgsql@j-davis.com>
To: =?ISO-8859-1?Q?Pi=F1eiro?= <apinheiro@igalia.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <1157998456.7540.15.camel@codfix.local.igalia.com>
References: <1157998456.7540.15.camel@codfix.local.igalia.com>
Content-Type: text/plain; charset=utf-8
Date: Mon, 11 Sep 2006 12:11:49 -0700
Message-Id: <1158001909.29889.33.camel@dogma.v10.wvs>
Mime-Version: 1.0
X-Mailer: Evolution 2.0.4 (2.0.4-7) 
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.24 tagged_above=0 required=5 tests=AWL,
	FORGED_RCVD_HELO, SPF_HELO_PASS
X-Spam-Level: 
X-Archive-Number: 200609/67
X-Sequence-Number: 20753

On Mon, 2006-09-11 at 20:14 +0200, Pi=C3=B1eiro wrote:
> Hi,
>=20
> a week ago we migrate a Woody(postgre 7.2.1) server to Sarge(postgre
> 7.4.7). To migrate the database we use a dump, using pg_dump with this
> options:
> pg_dump -U <username> -c -F p -O -v -f <filename> <DBname>
>=20
> We have a search, that using woody take about 1-2 minutes, but with
> sarge it is executing about 2 hours, and at least it crashes, with a
> message about a temporal file and no more disk space ( i have more than
> a GB of free disk space).
>=20

It sounds to me like it's choosing a bad sort plan, and unable to write
enough temporary disk files.

A likely cause is that you did not "vacuum analyze" after you loaded the
data. Try running that command and see if it helps. If not, can you
provide the output of "explain" and "explain analyze" on both the old
database and the new?

Also, I suggest that you upgrade to 8.1. 7.4 is quite old, and many
improvements have been made since then.

Regards,
	Jeff Davis