Re: full join in view

>
> Can you add some sql examples - table & index definition,
> view definition?
> If your view doesn't contain other views or sub-selects,
> postgres should
> use indexes.
> Tomasz Myrta
>

You are right. After disabling seq_scan, it uses indexes just as you described. Unfortunately my view happens to use subquery:

CREATE OR REPLACE VIEW v_tegelikud_kulud AS
SELECT
*,
COALESCE(dor_kst_id,kdt_kst_id) AS kst_id,
COALESCE(dor_mat_id,kdt_mat_id) AS mat_id
FROM (
SELECT
dor.dor_kst_id,
dor.dor_mat_id,
sum(dor.kogus * koefitsent::numeric) AS kogus,
sum(dor.kokku) AS kokku
FROM dokumentide_read dor
JOIN dokumendid dok
ON dor.dor_dok_id = dok.dok_id AND dok.tyyp = 30 AND dok.kinnitaja IS NOT NULL
GROUP BY
dor.dor_kst_id,
dor.dor_mat_id
) dor
FULL JOIN koostude_detailid kdt
ON dor.dor_mat_id = kdt.kdt_mat_id AND dor.dor_kst_id = kdt.kdt_kst_id;

The idea behind the view is to show supposed expenses (in table koostude_detailid) compared to actual expenses (in tables dokumendid and dokumentide_read). Both refer to materials (foreign keys kdt_mat_id and dor_mat_id) and belong to an assembly unit (foreign keys kdt_kst_id and dor_kst_id). The report will show supposed and actual expenses side by side for one assemby unit. So the view is queried like this:

explain select count(1) from v_tegelikud_kulud where kdt_kst_id = 1125 or dor_kst_id = 1125;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=42.31..42.31 rows=1 width=16)
-> Merge Join (cost=41.42..42.03 rows=113 width=16)
Merge Cond: (("outer".dor_kst_id = "inner".kdt_kst_id) AND ("outer".dor_mat_id = "inner".kdt_mat_id))
Filter: (("inner".kdt_kst_id = 1125) OR ("outer".dor_kst_id = 1125))
-> Sort (cost=34.44..34.46 rows=8 width=41)
Sort Key: dor.dor_kst_id, dor.dor_mat_id
-> Subquery Scan dor (cost=33.25..34.31 rows=8 width=41)
-> Aggregate (cost=33.25..34.31 rows=8 width=41)
-> Group (cost=33.25..33.89 rows=84 width=41)
-> Sort (cost=33.25..33.47 rows=84 width=41)
Sort Key: dor.dor_kst_id, dor.dor_mat_id
-> Hash Join (cost=8.19..30.56 rows=84 width=41)
Hash Cond: ("outer".dor_dok_id = "inner".dok_id)
-> Seq Scan on dokumentide_read dor (cost=0.00..15.61 rows=761 width=37)
-> Hash (cost=8.10..8.10 rows=36 width=4)
-> Seq Scan on dokumendid dok (cost=0.00..8.10 rows=36 width=4)
Filter: ((tyyp = 30) AND (kinnitaja IS NOT NULL))
-> Sort (cost=6.98..7.27 rows=113 width=8)
Sort Key: kdt.kdt_kst_id, kdt.kdt_mat_id
-> Seq Scan on koostude_detailid kdt (cost=0.00..3.13 rows=113 width=8)
(20 rows)

When I disable seqscan (I don't have many rows in our development database), I get following result:

explain select count(1) from v_tegelikud_kulud where kdt_kst_id = 1125 or dor_kst_id = 1125;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=51.13..51.13 rows=1 width=16)
-> Merge Join (cost=40.39..50.85 rows=113 width=16)
Merge Cond: (("outer".kdt_kst_id = "inner".dor_kst_id) AND ("outer".kdt_mat_id = "inner".dor_mat_id))
Filter: (("outer".kdt_kst_id = 1125) OR ("inner".dor_kst_id = 1125))
-> Index Scan using kdt_uk on koostude_detailid kdt (cost=0.00..10.13 rows=113 width=8)
-> Sort (cost=40.39..40.41 rows=8 width=41)
Sort Key: dor.dor_kst_id, dor.dor_mat_id
-> Subquery Scan dor (cost=39.20..40.26 rows=8 width=41)
-> Aggregate (cost=39.20..40.26 rows=8 width=41)
-> Group (cost=39.20..39.83 rows=84 width=41)
-> Sort (cost=39.20..39.41 rows=84 width=41)
Sort Key: dor.dor_kst_id, dor.dor_mat_id
-> Merge Join (cost=0.00..36.51 rows=84 width=41)
Merge Cond: ("outer".dor_dok_id = "inner".dok_id)
-> Index Scan using dor_dok_fk_i on dokumentide_read dor (cost=0.00..20.91 rows=761 width=37)
-> Index Scan using dok_pk on dokumendid dok (cost=0.00..12.56 rows=36 width=4)
Filter: ((tyyp = 30) AND (kinnitaja IS NOT NULL))
(17 rows)

As you see, condition dor_kst_id = 1125 is used after the subquery has done it's job (which may take a while...). And as I understand, currently I can do nothing about it?

Tambet

PS. Just to be complete, here are the descriptions of the tables used:

Table "public.koostude_detailid"
Column | Type | Modifiers
---------------+------------------------+-----------
kdt_id | integer | not null
kdt_kst_id | integer | not null
kdt_mat_id | integer | not null
detaili_nr | character varying(255) | not null
arv | numeric(5,0) | not null
kulu | numeric(16,6) | not null
yhik | character varying(10) | not null
koefitsent | real | not null
erikaal | numeric(16,6) | not null
eeldatav_hind | numeric(12,2) | not null
markused | character varying(255) |
Indexes: kdt_pk primary key btree (kdt_id),
kdt_detaili_nr_uk unique btree (kdt_kst_id, detaili_nr),
kdt_uk unique btree (kdt_kst_id, kdt_mat_id),
kdt_kst_fk_i btree (kdt_kst_id),
kdt_mat_fk_i btree (kdt_mat_id)
Check constraints: "kdt_arv_ck" (arv > 0::numeric)
"kdt_koefitsent_ck" (koefitsent > 0::double precision)
Foreign Key constraints: kdt_kst_fk FOREIGN KEY (kdt_kst_id) REFERENCES koostud(kst_id) ON UPDATE NO ACTION ON DELETE CASCADE,
kdt_mat_fk FOREIGN KEY (kdt_mat_id) REFERENCES materjalid(mat_id) ON UPDATE NO ACTION ON DELETE NO ACTION
Triggers: kdt_summa_juurde_trg,
kdt_summa_maha_trg

Table "public.dokumentide_read"
Column | Type | Modifiers
------------+------------------------+-----------
dor_id | integer | not null
dor_dok_id | integer | not null
dor_kst_id | integer |
dor_mat_id | integer | not null
kogus | numeric(16,6) |
koefitsent | real | not null
yhik | character varying(10) | not null
yhiku_hind | numeric(12,2) |
kokku | numeric(12,2) |
markused | character varying(255) |
Indexes: dor_pk primary key btree (dor_id),
dor_dok_fk_i btree (dor_dok_id),
dor_kst_fk_i btree (dor_kst_id),
dor_mat_fk_i btree (dor_mat_id)
Check constraints: "dor_koefitsent_ck" (koefitsent > 0::double precision)
Foreign Key constraints: dor_dok_fk FOREIGN KEY (dor_dok_id) REFERENCES dokumendid(dok_id) ON UPDATE NO ACTION ON DELETE CASCADE,
dor_mat_fk FOREIGN KEY (dor_mat_id) REFERENCES materjalid(mat_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
dor_kst_fk FOREIGN KEY (dor_kst_id) REFERENCES koostud(kst_id) ON UPDATE NO ACTION ON DELETE NO ACTION
Triggers: dor_summa_suurendamine_trg,
dor_summa_vahendamine_trg

Table "public.dokumendid"
Column | Type | Modifiers
------------------+-----------------------------+-----------
dok_id | integer | not null
dok_prt_id | integer |
dok_tot_id | integer |
dok_dok_id | integer |
tyyp | smallint | not null
dokumendi_nr | character varying(255) |
alusdokumendi_nr | character varying(255) |
kuupaev | date | not null
tahtaeg | date |
taidetud | date |
summa | numeric(12,2) | not null
markused | character varying(255) |
kinnitaja | character varying(255) |
kinnitamise_aeg | timestamp without time zone |
Indexes: dok_pk primary key btree (dok_id),
dok_dok_fk_i btree (dok_dok_id),
dok_dokumendi_nr_i btree (dokumendi_nr),
dok_kuupaev_i btree (kuupaev),
dok_prt_fk_i btree (dok_prt_id),
dok_tot_fk_i btree (dok_tot_id)
Check constraints: "dok_tyyp_ck" ((((((tyyp = 10) AND (dok_prt_id IS NOT NULL)) OR ((tyyp = 20) AND (dok_prt_id IS NOT NULL))) OR ((tyyp = 30
) AND (((dok_prt_id IS NOT NULL) AND (dok_tot_id IS NULL)) OR ((dok_tot_id IS NOT NULL) AND (dok_prt_id IS NULL))))) OR (tyyp = 40)) OR (tyyp
= 50))
Foreign Key constraints: dok_dok_fk FOREIGN KEY (dok_dok_id) REFERENCES dokumendid(dok_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
dok_prt_fk FOREIGN KEY (dok_prt_id) REFERENCES partnerid(prt_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
dok_tot_fk FOREIGN KEY (dok_tot_id) REFERENCES tootajad(tot_id) ON UPDATE NO ACTION ON DELETE NO ACTION