Reinder Menninga wrote: > Hi all, > > Thx for all the responses. I tried a few things like vacuuming the > tables and added index on insrel.rnumber. That didn't help. Specifying > the searchdir and role is always good but on most places we want to > retrieve all relations and not of just 1 role. For example to copy a > contentelement with all relations.
It's mainly about searchDir, though specifying a role can help mmbase optimizing away the 'or' in some cases (but of course never when about relations between objects of the same type). > The same queries ran very quickly on postgres 7.4 so I expected the > performance to be very much the same on a postgres 8. Making the queries > beter is good but it shouldnt be the reason of a performance lose of > over 1000% (not exaggerated). If the excact same query performs 10 times worse in postgresql 8, then in postgresql 7, then in may be a better idea to post that query to a postgresql related mailing list. > > > SELECT > > >contentelementen.notitie,contentelementen.owner,contentelementen.numb > > >er,contentelementen.vrijetekst6,contentelementen.datum2,contentelemen > > >ten.datum1,contentelementen.vrijetekst5,contentelementen.vrijetekst4, > > >contentelementen.vrijetekst3,contentelementen.vrijetekst2,contentelem > > >enten.vrijetekst1,contentelementen.elementtype,contentelementen.titel > > >,contentelementen.body,contentelementen.toontitel,contentelementen.ge > > >tal2,contentelementen.getal1,contentelementen.boolean2,contentelement > > >en.boolean1,contentelementen.otype,contentelementen.intro,root0.numbe > > >r,insrel.number,contentelementen.number > > >FROM cpb_contentelementen root0,cpb_insrel > > >insrel,cpb_contentelementen contentelementen WHERE root0.number=888 > > >AND ((root0.number=insrel.dnumber AND > > >contentelementen.number=insrel.snumber AND > > >insrel.dir<>1) OR (root0.number=insrel.snumber AND > > >contentelementen.number=insrel.dnumber)) LIMIT 1 The other thing which may be noticeble it that 'contentelementen' is likely a builder which very many extensions. Postgresql has a bit of a silly implementation for inheritance, which means that these kind of queries get proportionally more difficult with the number of extensions of the used table (or in this case perhaps even quadraticly?). Perhaps psql 8 is even worse than psql 7 in this respect? I was not aware of that, but who knows. If something like this is the case, then you're problably stuck, unless you contemplate mayor changes of the set-up, like migrating to an implementation which does not use the inheritance feature of postgresql but does that do itself. E.g. on the same way as the mysql-layer, or perhaps using views. Michiel -- Michiel Meeuwissen mihxil' Peperbus 107 MediaPark H'sum []() +31 (0)35 6772979 nl_NL eo_XX en_US _______________________________________________ Developers mailing list Developers@lists.mmbase.org http://lists.mmbase.org/mailman/listinfo/developers
