Date: Tue, 14 Jan 2003 21:02:33 +0100
To: Tambet Matiisen <t.matiisen@aprote.ee>
Cc: pgsql-sql@postgresql.org
Subject: Re: full join in view
Message-ID: <20030114200233.GA16310@serwer>
References: <81132473206F3A46A72BD6116E1A06AE1B14C2@black.aprote.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <81132473206F3A46A72BD6116E1A06AE1B14C2@black.aprote.com>
User-Agent: Mutt/1.3.28i
From: jasiek@klaster.net

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