Re: sub-select with aggregate

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=4146 width=20)
-> Aggregate (cost=0.00..4346.25 rows=4146 width=20)
-> Group (cost=0.00..4138.93 rows=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=4146 width=20)
-> Aggregate (cost=0.00..4346.25 rows=4146 width=20)
-> Group (cost=0.00..4138.93 rows=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