Re: full join in view - Mailing list pgsql-sql

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-depthanalysis 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
anew 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
NOTNULL       )
 
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
actuallyneeded.
 
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.
MaybeI 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
triedto move it to subquery, but then the optimizer chose a totally different execution plan and seemingly was not able
touse indeces of "dokumentide_read" table. The version with subquery:
 
>
Now it should work better.

Tomasz Myrta