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.
