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.
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). I will look into the queries to eliminate the or's but I do not expect much of it.. Mvg, Reinder Kennisnet.nl -----Oorspronkelijk bericht----- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Namens Michiel Meeuwissen Verzonden: donderdag 21 december 2006 10:41 Aan: Discussion list for developers Onderwerp: Re: [Developers] postgres Johannes Verelst wrote: > Hi Reinder, > > > 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 > > There might be reasons on postgres level that are causing that, but > note that these queries with an OR inside them are always heavy, on > all databases. These are generated by pieces of MMBase coe that don't > use a 'search direction'; so instead of just finding a parent or a > child, this will find all contentelements for which this element is > either the parent OR the child. > > Databases I know (MySQL and informix) don't like this. You far far > better of doing two seperate queries, or doing a union. For Informix > there is some database-support-code that exactly does this: generate a > union for two seperate queries. > > So, if possible, try to change your query (for instance use the > 'searchdir' parameter on the <mm:related> tags). That will most > definately help more than any database tuning. Yes, and as we did last week, it is indeed possible to achieve to excact same result using 2 list-tags (with searchdir='source' and searchdir='destination), and using the 'add' and 'comparator' attribute on the second one :-) Of course that is a bit overly elaborate, and it may be a nice idea to change mmbase that in certain situations it would opt for such an explicit union automaticly. But for the moment indeed the best advice you can follow is to eliminate all these OR queries, which effectively means that if you follow relations between objects of the same type you _must always use the searchdir attribute_. For relations between object of different types, it cannot harm, but it will go automaticly well if you object-model is clean, and allows for the relation to be in one direction only. Michiel -- Michiel Meeuwissen mihxil' Peperbus 107 MediaPark H'sum []() +31 (0)35 6772979 nl_NL eo_XX en_US _______________________________________________ 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
