Sorry I answer the message only to Scott Marlowe. I re-send the response --------- Mensaje reenviado -------- De: Piñeiro <apinheiro@xxxxxxxxxx> Para: Scott Marlowe <smarlowe@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxx>
