Re: full join in view - Mailing list pgsql-sql

>
> 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
tablesdokumendid and dokumentide_read). Both refer to materials (foreign keys kdt_mat_id and dor_mat_id) and belong to
anassembly unit (foreign keys kdt_kst_id and dor_kst_id). The report will show supposed and actual expenses side by
sidefor 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.89rows=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=761width=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=113width=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=84width=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=36width=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
asI 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 nullkdt_kst_id    |
integer               | not nullkdt_mat_id    | integer                | not nulldetaili_nr    | character varying(255)
|not nullarv           | numeric(5,0)           | not nullkulu          | numeric(16,6)          | not nullyhik
| character varying(10)  | not nullkoefitsent    | real                   | not nullerikaal       | numeric(16,6)
  | not nulleeldatav_hind | numeric(12,2)          | not nullmarkused      | character varying(255) | 
Indexes: kdt_pk primary key btree (kdt_id),        kdt_detaili_nr_uk unique btree (kdt_kst_id, detaili_nr),
kdt_ukunique 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
DELETENO ACTION 
Triggers: kdt_summa_juurde_trg,         kdt_summa_maha_trg
        Table "public.dokumentide_read"  Column   |          Type          | Modifiers
------------+------------------------+-----------dor_id     | integer                | not nulldor_dok_id | integer
          | not nulldor_kst_id | integer                |dor_mat_id | integer                | not nullkogus      |
numeric(16,6)         |koefitsent | real                   | not nullyhik       | character varying(10)  | not
nullyhiku_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
DELETECASCADE,                        dor_mat_fk FOREIGN KEY (dor_mat_id) REFERENCES materjalid(mat_id) ON UPDATE NO
ACTIONON DELETE NO ACTION,                        dor_kst_fk FOREIGN KEY (dor_kst_id) REFERENCES koostud(kst_id) ON
UPDATENO ACTION ON DELETE NO ACTION 
Triggers: dor_summa_suurendamine_trg,         dor_summa_vahendamine_trg
                Table "public.dokumendid"     Column      |            Type             | Modifiers
------------------+-----------------------------+-----------dok_id           | integer                     | not
nulldok_prt_id      | integer                     |dok_tot_id       | integer                     |dok_dok_id       |
integer                    |tyyp             | smallint                    | not nulldokumendi_nr     | character
varying(255)     |alusdokumendi_nr | character varying(255)      |kuupaev          | date                        | not
nulltahtaeg         | date                        |taidetud         | date                        |summa            |
numeric(12,2)              | not nullmarkused         | 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
DELETENO ACTION,                        dok_prt_fk FOREIGN KEY (dok_prt_id) REFERENCES partnerid(prt_id) ON UPDATE NO
ACTIONON DELETE NO ACTION,                        dok_tot_fk FOREIGN KEY (dok_tot_id) REFERENCES tootajad(tot_id) ON
UPDATENO ACTION ON DELETE NO ACTION