Subject: [Fwd: Re: Performance problem with Sarge compared with
	Woody]
From: =?ISO-8859-1?Q?Pi=F1eiro?= <apinheiro@igalia.com>
To: PSQL-Performance <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable
Date: Tue, 12 Sep 2006 18:06:46 +0200
Message-Id: <1158077206.4349.9.camel@codfix.local.igalia.com>
Mime-Version: 1.0

Sorry I answer the message only to Scott Marlowe. I re-send the response

--------- Mensaje reenviado --------
De: Pi=F1eiro <apinheiro@igalia.com>
Para: Scott Marlowe <smarlowe@g2switchworks.com>
Asunto: Re: [PERFORM] Performance problem with Sarge compared with Woody
Fecha: Tue, 12 Sep 2006 17:36:41 +0200
El mar, 12-09-2006 a las 09:27 -0500, Scott Marlowe escribi=F3:
> On Tue, 2006-09-12 at 02:18, Pi=F1eiro wrote:
> > El lun, 11-09-2006 a las 17:07 -0500, Scott Marlowe escribi=F3:


> The 7.2.x query planner, if I remember correctly, did ALL The join ons
> first, then did the joins in the where clause in whatever order it
> thought best.
>=20
> Starting with 7.3 or 7.4 (not sure which) the planner was able to try
> and decide which tables in both the join on() syntax and with where
> clauses it wanted to run.
>=20
> Is it possible to fix the strangness of the ERP so it doesn't do that
> thing where it puts a lot of unconstrained tables in the middle of the
> from list?  Also, moving where clause join condititions into the join
> on() syntax is usually a huge win.
Well, I'm currently one of the new version of this ERP developer, but
I'm a "recent adquisition" at the staff. I don't take part at the
developing of the old version, and manage how the application creates
this huge query could be a madness.

>=20
>   I'd probably put 8.1.4 (or the latest 8.2 snapshot) on a test box and
> see what it could do with this query for an afternoon.  It might run
> just as slow, or it might "get it right" and run it in a few seconds.=20
> While there are the occasions where a query does run slower when
> migrating from an older version to a newer version, the opposite is
> usually true.  From 7.2 to 7.4 there was a lot of work done in "getting
> things right" and some of this caused some things to go slower, although
> not much.

I tried recently to execute this query on a database installed on a
laptop with 256 MB RAM, ubuntu, and the 8.0.7 postgreSQL version, and I
don't solve nothing... well the next try will be use 8.1.4


> > There are any difference between 7.2.1 and 7.4.2 versions about this?
> > With the 7.4.2 there are more indices, or there was duplicated indices
> > with the woody version too?=20
> > (before you comment this: yes I try to remove the duplicate indices to
> > check if this was the problem)
>=20
> Wait, are you running 7.4.2 or 7.4.7?  7.4.7 is bad enough, but 7.4.2 is
> truly dangerous.  Upgrade to 7.4.13 whichever version you're running.
>=20
Sorry a mistmatch, we are using the sarge postgre version, 7.4.7


--=20
Pi=F1eiro <apinheiro@igalia.com>