Re: sub-select with aggregate - Mailing list pgsql-sql
From | Tomasz Myrta |
---|---|
Subject | Re: sub-select with aggregate |
Date | |
Msg-id | 3DB7BF5A.1090606@klaster.net Whole thread Raw |
In response to | Re: sub-select with aggregate (Stephan Szabo <sszabo@megazone23.bigpanda.com>) |
Responses |
Re: sub-select with aggregate
|
List | pgsql-sql |
Uz.ytkownik Stephan Szabo napisa?: > I see these two queries that in 7.3 push the clause into the > subquery and I believe should have the same output: > > create view v as > select > group_id, > a/sum_a as percent_a, > b/sum_b as percent_b > from > (select > group_id, > sum(a) as sum_a, > sum(b) as sum_b > from users group by group_id) X join > users using (group_id); > > and > > create view v as > select > X.group_id, > a/sum_a as percent_a, > b/sum_b as percent_b > from > users join > (select > group_id, > sum(a) as sum_a, > sum(b) as sum_b > from users group by group_id) X > on (X.group_id=users.group_id); I made tests of these queries (Postgres 7.2). In my database there are master table "bilety" and detail "przejazdy": 1) Exposing field from sub-query drop view v; create view v as select X.id_biletu, netto, vat from bilety B join (select id_biletu, sum(netto) as netto, sum(vat) as vat from przejazdy group by id_biletu) X on (X.id_biletu=B.id_biletu); explain select * from v where id_biletu=12345; Nested Loop (cost=0.00..29.58 rows=5 width=24) -> Subquery Scan x (cost=0.00..7.94 rows=1 width=20) -> Aggregate (cost=0.00..7.94 rows=1 width=20) -> Group (cost=0.00..7.93 rows=1 width=20) -> Index Scan using qq2 on przejazdy (cost=0.00..7.92 rows=1 width=20) -> Index Scan using ind_bil_id on bilety b (cost=0.00..21.58 rows=5 width=4) 2) Exposing field from master-table drop view v; create view v as select X.id_biletu, netto, vat from bilety B join (select id_biletu, sum(netto) as netto, sum(vat) as vat from przejazdy group by id_biletu) X on (X.id_biletu=B.id_biletu); explain select * from v where id_biletu=12345; Merge Join (cost=4595.39..4627.36 rows=3 width=24) -> Index Scan using ind_bil_id on bilety b (cost=0.00..21.55 rows=5 width=4) -> Sort (cost=4595.39..4595.39 rows=4146 width=20) -> Subquery Scan x (cost=0.00..4346.25 rows=4146width=20) -> Aggregate (cost=0.00..4346.25 rows=4146 width=20) -> Group (cost=0.00..4138.93rows=41463 width=20) -> Index Scan using qq2 on przejazdy (cost=0.00..4035.28 rows=41463 width=20) 3) Using planner to choose exposed field drop view v; create view v as select id_biletu, netto, vat from bilety B join (select id_biletu, sum(netto) as netto, sum(vat) as vat from przejazdy group by id_biletu) X using(id_biletu); explain select * from v where id_biletu=12345; Merge Join (cost=4595.39..4627.36 rows=3 width=24) -> Index Scan using ind_bil_id on bilety b (cost=0.00..21.55 rows=5 width=4) -> Sort (cost=4595.39..4595.39 rows=4146 width=20) -> Subquery Scan x (cost=0.00..4346.25 rows=4146width=20) -> Aggregate (cost=0.00..4346.25 rows=4146 width=20) -> Group (cost=0.00..4138.93rows=41463 width=20) -> Index Scan using qq2 on przejazdy (cost=0.00..4035.28 rows=41463 width=20) Now I know the solution - to speed up this query I have to manually expose field from sub-query. It works fine, but I still don't know why I can't do this opposite way. Tomasz Myrta