From: Markus Schulz <msc@antzsystem.de>
Organization: AntzSystem GmbH
To: pgsql-performance@postgresql.org
Subject: problem with from_collapse_limit and joined views
Date: Wed, 24 Nov 2010 19:37:07 +0100
User-Agent: KMail/1.13.5 (Linux/2.6.36-rc6-686-bigmem; KDE/4.4.5; i686; ; )
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <201011241937.08622@Mail-Followup-To>

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