Sorry I answer the message only to Scott Marlowe. I re-send the response
--------- Mensaje reenviado --------
De: Piñeiro <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ó:
> On Tue, 2006-09-12 at 02:18, Piñeiro wrote:
> > El lun, 11-09-2006 a las 17:07 -0500, Scott Marlowe escribió:
> 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.
>
> 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.
>
> 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.
>
> 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.
> 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?
> > (before you comment this: yes I try to remove the duplicate indices to
> > check if this was the problem)
>
> 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.
>
Sorry a mistmatch, we are using the sarge postgre version, 7.4.7
--
Piñeiro <apinheiro@igalia.com>