From: Tomasz Myrta <jasiek@klaster.net>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: sub-select with aggregate
Date: Thu, 24 Oct 2002 11:37:30 +0200
Message-ID: <3DB7BF5A.1090606@klaster.net> (raw)
References: <20021023071418.N4419-100000@megazone23.bigpanda.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasiek@klaster.net, sszabo@megazone23.bigpanda.com
Subject: Re: sub-select with aggregate
In-Reply-To: <3DB7BF5A.1090606@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox