Re: full join in view

On Tue, Jan 14, 2003 at 04:27:22PM +0200, Tambet Matiisen wrote:
>
> First I would like to say, that I'm quite amazed. You even guessed table names right! :) I did not expect such an in-depth analysis in such a short time. Thanks, Tomasz!
It wasn't difficult - these names where in foreign keys definition.
>
> >
> >
> > Tambet Matiisen wrote:
> >
> >
> > Is dor_kst_id the same as kdt_kst_id and as mat_id? After
> > some database
> > practicing I found, that using the same name in all tables is
> > much more
> > comfortably
> >
> This way I can refer most columns without prefixing them with table alias. But it's anyway good habit to use table aliases, so this is not that important. I think in next project I try it in your way.

If you have joins like this:
table1 join table2 using (field1)
duplicates of field1 disappears and you don't need table name.
>
> > For each material (materjalid) and koostud (koostud) you want to find
> > some current value (koostude_detaild) and compare it to some sum
> > (documentid...)?
> > I'm not sure if I understand well your view, but here is my
> > version of
> > this view - without subquery:
> >
>
> I tried to save few scans by not including "koostud" and "materjalid" in my original query. Based on yours, I created a new version:

If you need only not null-kdt_id and dor_id, then just change your joins
into inner joins.

If you are sure, that you will get only not-null results, you don't need
to include koostud and marerjalid.

The result is:

CREATE OR REPLACE VIEW v_tegelikud_kulud2 AS
SELECT
kdt.kdt_kst_id as kst_id,
kdt.kdt_mat_id as mat_id,
max(kdt.detaili_nr) AS detaili_nr,
max(kdt.arv) AS arv,
max(kdt.kulu) AS kulu,
max(kdt.yhik) AS yhik,
max(kdt.koefitsent) AS koefitsent,
max(kdt.eeldatav_hind) AS eeldatav_hind,
sum(dor.kogus * dor.koefitsent::numeric) AS kogus,
sum(dor.kokku) AS kokku
FROM
koostude_detailid kdt
JOIN dokumentide_read dor
ON kdt.kdt_kst_id = dor.dor_kst_id AND kdt.kdt_mat_id = dor.dor_mat_id AND EXISTS
(
SELECT 1
FROM dokumendid dok
WHERE dor.dor_dok_id = dok.dok_id AND dok.tyyp = 30 AND dok.kinnitaja IS NOT NULL
)
GROUP BY kst.kst_id, mat.mat_id;

> But there are still few things that worry me:
> 1. Cross join between koostud and materjalid. Table "materjalid" may have up to 10000 rows and only 20-30 of them are actually needed.
You don't need it anymore. Anyway I thought, that you have in your query
"mat_id=.. and kst_id=.."
> 2. Indeces on "koostude_detailid" and "dokumentide_read" are not used. Probably my tables do not contain enough rows. Maybe I should generate more test data first.
> 3. The cost of this query is twice as big, as my original query. It seems to me, that SubPlan is causing this. I tried to move it to subquery, but then the optimizer chose a totally different execution plan and seemingly was not able to use indeces of "dokumentide_read" table. The version with subquery:
>
Now it should work better.

Tomasz Myrta