Re: [Fwd: Re: Performance problem with Sarge compared

On Tue, 2006-09-12 at 11:06, Piñeiro wrote:
> --------- Mensaje reenviado --------
> De: Piñeiro <apinheiro(at)igalia(dot)com>
> Para: Scott Marlowe <smarlowe(at)g2switchworks(dot)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

OK, I'm gonna guess that 8.1 or 8.2 will likely not fix your problem, as
it's likely that somewhere along the line the planner is making some
inefficient unconstrained join on your data in some intermediate step.

As Tom asked, post the explain analyze output for this query. I'm
guessing there'll be a stage that is creating millions (possibly upon
millions) of rows from a cross product.