From: | Tomasz Myrta <jasiek(at)klaster(dot)net> |
---|---|
To: | Tambet Matiisen <t(dot)matiisen(at)aprote(dot)ee> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: full join in view |
Date: | 2003-01-14 09:50:58 |
Message-ID: | 3E23DD82.5050208@klaster.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Tambet Matiisen wrote:
> You are right. After disabling seq_scan, it uses indexes just as you
> described. Unfortunately my view happens to use subquery:
Don't disable seq_scan - sometimes it is better than indexscan.
I had the same problem as you - find subject "sub-select with aggregate"
on pgsql-sql mailing list dated on 2002-10-23.
In my case exposing fields from subquery solved my problem.
There is one more problem in your query - coalesce, which possibly
disables any indexing in your view. Try to rewrite your view - subquery
shouldn't return dor_kst_id and dor_mat_id null.
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
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:
CREATE OR REPLACE VIEW v_tegelikud_kulud AS
SELECT
koostud.kst_id,
materjalid.mat_id,
sum(dor.kogus * koefitsent::numeric) AS kogus,
sum(dor.kokku) AS kokku,
<fields>
FROM
koostud k ,
materjalid m
left join koostude_detailid kdt
ON (m.mat_id = kdt.kdt_mat_id AND k.kst_id = kdt.kdt_kst_id)
left join dokumentide_read dor
ON (m.mat_id = dor.dor_mat_id AND k.kst_id = dor.dor_kst_id)
left JOIN dokumendid dok
ON (dor.dor_dok_id = dok.dok_id AND dok.tyyp = 30 AND
dok.kinnitaja IS NOT NULL)
group by koostud.kst_id, materjalid.mat_id, <fields>;
One more hint - create two-fields-indexes on koostude_detailid and
dokuemntide_read (kdt_mat_id,kdt_kst_id)
Regards,
Tomasz Myrta
From | Date | Subject | |
---|---|---|---|
Next Message | Pedro Igor | 2003-01-14 10:53:45 | Data between different databases |
Previous Message | Tambet Matiisen | 2003-01-14 09:04:21 | Re: full join in view |