hello, i have a big performance problem with some views which would joined (from the third party tool crystal reports) to print a document. view1: SELECT ... FROM personen.kunde kunde, personen.natuerliche_person person, viewakteur akteur, personen.anschrift adresse, personen.kontaktdaten kontakt, konten.bankverbindung konto, personen.berufsdaten beruf WHERE person.objid = kunde.objid AND akteur.objid = kunde.objid AND person.adresse = adresse.objid AND person.kontaktdaten = kontakt.objid AND person.bankverbindung = konto.objid AND person.berufsdaten = beruf.objid view2: SELECT ... FROM vertraege.vertrag basisvertrag JOIN .. .. twelve more inner joins .. Each view works alone very fast for objid-access.(no sequence scans) The final query build by crystal reports was like: SELECT ... FROM view2 INNER JOIN view1 ON view2.kunde_objid = view1.objid WHERE view2.objid = XXXX as you can see the search-key for view1 comes from view2. if i set "from_collapse_limit" (to merge the views) and join_collapse_limit (to explode the explicit joins) high enough(approx 32), all is fine (good performance). But other queries are really slow in our environment (therefore it's no option to raise the join_collapse_limit to a higher value) With defaults (8) for both, the performance is ugly because pgsql can't explode the views to build a better join-table with view1. (basisvertrag.kunde_objid from view2 is the key for kunde.objid from view1). As workaround nr.1 i can do the following: SELECT ... FROM view2 INNER JOIN view1 ON view2.kunde_objid = view1.objid WHERE view2.objid = XXXX AND view1.objid = YYYY yyyy (redundant information) is the same value as view2.kunde_objid. This instructs pgsql to minimize the result of view1 (one entry). But for this solution i must change hundreds of crystal report files. For workaround nr.2 i need to instruct crystal report to generate a cross-join: SELECT ... FROM view2 , view1 WHERE view2.VNID = view1.ID AND view2.ID = XXXX Then i can slightly increase the from_collapse_limit (9) to enforce pgsql to explode the view1 and build a better join-plan. But i don't find a way to enforce crystal reports to using cross joins. Workaround nr.3: build one big view which contains all parts of view1 and view2. Really ugly (view1 and view2 are used in many more places). What are the other options? Regards, msc -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
