Michiel Meeuwissen wrote: > 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).
It also helps the database (provided there's an index on rnumber), as the number of qualifying rows in insrel will be reduced by a vast amount. This in turn reduces the size of the join with the related tables quadratically with the reduction in the number of qualifying rows in insrel. >> 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. And take a look at the various resources about query- and database performance. Google is also your friend ;) IIRC, the bad performance of joins over inherited tables is on the todo list for PG 8.3. The alternative using views (like the MSSQL mmbase implementation) used to perform quite badly as well, but this is allegedly fixed in PG 8.2. We modified the PG driver for MMBase to use views instead of inheritance, and to not join with tables of which no attributes were requiested. MMBase does a huge amount of self-joins on mm_object (any object is an mm_object, after all), but until recently PG's query planner got confused by the views. With our current implementation (with mentioned modifications) on PG 8.1 performance is actually better than it used to be on MySQL. >>>> 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?). Not silly really; it behaves exactly the way MMBase expects. The implementation is incomplete, though - referential integrity is not inherited because the developers haven't yet decided on how to cope with the inherent complexities. And the query planner doesn't cope too well with the resulting data structures. It has it's uses, but MMBase isn't one of them. > Perhaps psql 8 is even worse than psql 7 in this respect? I was not aware > of that, but who knows. Slow queries are usually because of a bad query plan. Inspecting the query plan (EXPLAIN ANALYZE) will give you hints on what is wrong. It's probably doing a bunch of sequential scans. The query plan will also show whether the planners estimates reflect the actual data (a VACUUM ANALYZE is needed if this differs a lot). That much of a difference is an almost sure sign of memory starvation, usually because the database wasn't configured to use any. > 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. In our experience views work well, but I think an implementation like the mysql-layer may actually perform better (though inserts/updates will be slower due to the extra overhead, of course). Oh... Forget about using triggers to pre-calculate data. It confuses the hell out of MMBases caches... -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // _______________________________________________ Developers mailing list [email protected] http://lists.mmbase.org/mailman/listinfo/developers
