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=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