Message-ID: <3E23DD82.5050208@klaster.net>
Date: Tue, 14 Jan 2003 10:50:58 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; en-US;
	rv:1.2b) Gecko/20021016
MIME-Version: 1.0
To: Tambet Matiisen <t.matiisen@aprote.ee>
Cc: pgsql-sql@postgresql.org
Subject: Re: full join in view
References: <81132473206F3A46A72BD6116E1A06AE1B14C0@black.aprote.com>
In-Reply-To: <81132473206F3A46A72BD6116E1A06AE1B14C0@black.aprote.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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