problem with from_collapse_limit and joined views - Mailing list pgsql-performance

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