From: Markus Schulz <msc@antzsystem.de>
To: pgsql-performance@postgresql.org
Subject: problem with from_collapse_limit and joined views
Date: Wed, 24 Nov 2010 19:37:07 +0100
Message-ID: <201011241937.08622@Mail-Followup-To> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: msc@antzsystem.de
Subject: Re: problem with from_collapse_limit and joined views
In-Reply-To: <201011241937.08622@Mail-Followup-To>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox