Re: [SQL] sub-select with aggregate

[Tomasz Myrta]

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html