X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id CBBDB9FA231
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 11 Sep 2006 19:03:52 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 85820-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Mon, 11 Sep 2006 22:03:44 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from exchange.g2switchworks.com (mail.g2switchworks.com
	[63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 17BF69F9F79
	for <pgsql-performance@postgresql.org>;
	Mon, 11 Sep 2006 19:03:43 -0300 (ADT)
Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com
	([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ;
	Mon, 11 Sep 2006 22:03:42 +0000
Received: from state.g2switchworks.com by mail.g2switchworks.com;
	11 Sep 2006 17:03:41 -0500
Subject: Re: Performance problem with Sarge compared with Woody
From: Scott Marlowe <smarlowe@g2switchworks.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
Content-Transfer-Encoding: quoted-printable
Message-Id: <1158012220.1854.10.camel@state.g2switchworks.com>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) 
Date: Mon, 11 Sep 2006 17:03:41 -0500
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=SPF_PASS,
	UNPARSEABLE_RELAY
X-Spam-Level: 
X-Archive-Number: 200609/68
X-Sequence-Number: 20754

On Mon, 2006-09-11 at 13:14, 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
> The search is very long, with a lot of joins (generated by a ERP we
> manage). We think that the problem can be at the indices, but we are not
> sure. At the original woody database we create indices, but when the
> dump is being installed at sarge, it creates an implicit index, so there
> are times that there are duplicates indices. But we try to remove the
> duplicate indices and we don't resove the problem.

That query made my head hurt.  However, reading as much of it as I could
make myself, it seemed to have the common problem where it has lots of
tables in the middle of the joins, i.e.

select <select list> from
table1 join table2 on (...
join table3, table4, table5=20
left join table 6 on (table2.xx =3D table6.yy)
where table3=3D...

So, the theoretical way to create this is to first join table1 to
table2, then table3, table4, and table5 with NO CONSTRAINT then table6,
then separate out all the rows from that huge unconstrained join with
the where clause. =20

I'd suggest two things.

one:  Get a better ERP... :)  or at least one you can inject some
intelligence into, and two: upgrade to postgresql 8.1, or even 8.2 which
will be released moderately soon, and if you won't be going into
production directly, might be ready about the time you are.