Re: full join in view
От | Tambet Matiisen |
---|---|
Тема | Re: full join in view |
Дата | |
Msg-id | 81132473206F3A46A72BD6116E1A06AE1B14C0@black.aprote.com обсуждение исходный текст |
Ответ на | full join in view ("Tambet Matiisen" <t.matiisen@aprote.ee>) |
Ответы |
Re: full join in view
|
Список | 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
В списке pgsql-sql по дате отправления: