To improve the performance of joins in Postgresql it is also recommended to run an 'ANALYZE' on your db on a regular basis. This way the Postgresql optimizer is informed about table sizes and other statistics so that it can better optimize it's execution plans. Quite relevant when you have long path's in mm:lists.
Analyze can be run manually in psql or through phpPgAdmin. cheers, Peter Reitsma 06-47422310 www.reitsma.ru > -----Oorspronkelijk bericht----- > Van: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Namens Michiel Meeuwissen > Verzonden: woensdag 14 januari 2004 17:53 > Aan: [EMAIL PROTECTED] > Onderwerp: BasicSqlHandler > > > > I was exploring the possibility to do some query optimizing. > We use for example PostgreSql and e.g. > http://www.postgresql.org/docs/7.2/static/explicit-joins.html > > suggests that a dumb simple join might not be very optimal, > and that it might be better to state the query a bit more explicitely. > > It is clear that it is suboptimal now, because a large join > of 6 or 7 deep (which is really easy to make with MMBase) can > indeed perform very lousy now. > > So I was looking to BasicSqlHandler to see if I could smartly > override some things for PostgreSqlSqlHandler to make it > perform better. > > The problem with BasicSqlHandler is however that it consists > of a few gigantic methods (like > 'appendQueryBodyToSql',appendConstraintsToSql) doing much of > the work. > > Would it be an idea to split these methods somehow up in > smaller pieces, which would then be overridable a bit more easily? > > Perhaps this is not very urgent for the release of 1.7 > because we never did this very good, and we don't need to be > perfect from one day to the other. > > But, I suggest that after the release the long awaited > 'performance' project will finally be surrected, to look into > these kind of things. > > > Michiel > > PS. What _is_ more urgent now is automaticly creating indices > also in Postgresql which seems to be impossible now because > postgresql requires indices to be made with seperate queries > (not in the create table statement). Bug #6214. > > This should be implemented, if not for performeance itself, > then to prove that the new storage framework is indeed ok. > > > > -- > Michiel Meeuwissen > Mediapark C101 Hilversum > +31 (0)35 6772979 > nl_NL eo_XX en_US > mihxil' > [] () > >
