Yes perhaps postgres 8 is even worse with query plans and inherited tables. I tried EXPLAIN ANALYZE with the slow query but it doesn't come to an end. It ran all night and it was still not finished..
I think if we want to use postgres 8 we should do mayor changes to the set-up such as using views as you suggested. Thx for the response, any other good ideas are of course welcome ;) Greets, Reinder Kennisnet.nl -----Oorspronkelijk bericht----- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Namens Alban Hertroys Verzonden: donderdag 21 december 2006 13:58 Aan: Michiel Meeuwissen; Discussion list for developers Onderwerp: Re: [Developers] postgres 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.nu >>>> mb >>>> er,contentelementen.vrijetekst6,contentelementen.datum2,contentelem >>>> en >>>> ten.datum1,contentelementen.vrijetekst5,contentelementen.vrijetekst >>>> 4, >>>> contentelementen.vrijetekst3,contentelementen.vrijetekst2,contentel >>>> em >>>> enten.vrijetekst1,contentelementen.elementtype,contentelementen.tit >>>> el >>>> ,contentelementen.body,contentelementen.toontitel,contentelementen. >>>> ge >>>> tal2,contentelementen.getal1,contentelementen.boolean2,contenteleme >>>> nt >>>> en.boolean1,contentelementen.otype,contentelementen.intro,root0.num >>>> be 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 DISCLAIMER: Dit bericht (met bijlagen) is met grote zorgvuldigheid samengesteld. Voor mogelijke onjuistheid en/of onvolledigheid van de hierin verstrekte informatie kan Stichting Kennisnet Ict op School geen aansprakelijkheid aanvaarden, evenmin kunnen aan de inhoud van dit bericht (met bijlagen) rechten worden ontleend. De inhoud van dit bericht (met bijlagen) kan vertrouwelijke informatie bevatten en is uitsluitend bestemd voor de geadresseerde van dit bericht. Indien u niet de beoogde ontvanger van dit bericht bent, verzoekt Stichting Kennisnet Ict op School u dit bericht te verwijderen, eventuele bijlagen niet te openen en wijst Stichting Kennisnet Ict op School u op de onrechtmatigheid van het gebruiken, kopiëren of verspreiden van de inhoud van dit bericht (met bijlagen). This message (with attachments) is given in good faith. Stichting Kennisnet Ict op School cannot assume any responsibility for the accuracy or reliability of the information contained in this message (with attachments), nor shall the information be construed as constituting any obligation on the part of Stichting Kennisnet Ict op School. The information contained in this message (with attachments) may be confidential or privileged and is only intended for the use of the named addressee. If you are not the intended recipient, you are requested by Stichting Kennisnet Ict op School to delete this message (with attachments) without opening it and you are notified by Stichting Kennisnet Ict op School that any disclosure, copying or distribution of the information contained in this message (with attachments) is strictly prohibited and unlawful. _______________________________________________ Developers mailing list [email protected] http://lists.mmbase.org/mailman/listinfo/developers
