From: jasiek@klaster.net
To: Tambet Matiisen <t.matiisen@aprote.ee>
Cc: pgsql-sql@postgresql.org
Subject: Re: full join in view
Date: Tue, 14 Jan 2003 21:02:33 +0100
Message-ID: <20030114200233.GA16310@serwer> (raw)
In-Reply-To: <81132473206F3A46A72BD6116E1A06AE1B14C2@black.aprote.com>
References: <81132473206F3A46A72BD6116E1A06AE1B14C2@black.aprote.com>
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
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-sql@postgresql.org
Cc: jasiek@klaster.net, t.matiisen@aprote.ee
Subject: Re: full join in view
In-Reply-To: <20030114200233.GA16310@serwer>
* 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