Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id D40F4475FB5
	for <pgsql-sql@postgresql.org>; Thu, 24 Oct 2002 05:31:22 -0400 (EDT)
Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66])
	by postgresql.org (Postfix) with ESMTP id B029D475F35
	for <pgsql-sql@postgresql.org>; Thu, 24 Oct 2002 05:31:21 -0400 (EDT)
Received: from klaster.net (pd53.krakow.cvx.ppp.tpnet.pl [213.76.39.53])
	by serwer.skawsoft.com.pl (Postfix) with ESMTP
	id E3ADF2B4D6; Thu, 24 Oct 2002 11:24:39 +0200 (CEST)
Message-ID: <3DB7BF5A.1090606@klaster.net>
Date: Thu, 24 Oct 2002 11:37:30 +0200
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; PL; rv:1.1) Gecko/20020826
X-Accept-Language: pl, en-us, en
MIME-Version: 1.0
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: sub-select with aggregate
References: <20021023071418.N4419-100000@megazone23.bigpanda.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200210/328
X-Sequence-Number: 10045

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