Re: Performance problem with Sarge compared with Woody

On Tue, 2006-09-12 at 02:18, Piñeiro wrote:
> El lun, 11-09-2006 a las 17:07 -0500, Scott Marlowe escribió:
>
> > Also also, you should be running at LEAST 7.4.13, the latest release of
> > 7.4.  It's possible there's a fix between 7.4.7 and 7.4.13 that fixes
> > your problem.  Doubt it, but it could be.  However, the more important
> > point is that there are REAL data eating bugs in 7.4.7 that may take a
> > bite out of your data.
> First, thanks for all your answers.
>
> About your comments:
>    * Yes, i have executed VACUUM FULL ANALYZE VERBOSE after the dump,
> and after all my tries to solve this.
>
>    * About another ERP: this ERP is one developed by us, we are
> developing the next version, but until this is finished we need to
> maintain the old one, with all his problems (as the "montrous" selects).

I feel your pain.  I've written a few apps that created queries on the
fly that quickly grew into monstrosities that stomped my pg servers into
the ground.

>    * About Postgre version: you advice me to upgrade from 7.4.7 (postgre
> version at sarge) to 8.2. Well, I don't want to be a troll, but I
> upgrade from 7.2.1 (woody) to 7.4.7 and I get worse, do you really think
> that upgrade to 8.1 will solve something?

It's likely that something in 7.4.7 is happening as a side effect.

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.

  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.

From 7.4 to 8.1 (and now 8.2) a lot of focus has been on optimizing the
query planner and adding methods of joining that have made huge strides
in performance.

However, running 7.4.7 instead of 7.4.13 is a mistake, 100%.  Updates
happen for a reason, reasons like your data could get eaten, or the
query planner makes a really stupid decision that causes it to take
hours to run a query...  You can upgrade from 7.4.7 to 7.4.13 in place,
no need to dump and restore (take a backup just in case, but that's a
given).

> About the indices:
>   I comment previously that I think that the problem could be at the
> indices. Well, at the woody postgre version we add all the indices by
> hand, including the primary key index. The dump takes all these and
> inserts at the sarge version, but sarge inserts an implicit index using
> the primary key, so at the sarge version we have duplicate indices.

Probably not a big issue.

> 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.