To: Tomasz Myrta <jasiek@klaster.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: sub-select with aggregate 
In-reply-to: <3DB674CD.50404@klaster.net> 
References: <3DB674CD.50404@klaster.net>
Comments: In-reply-to Tomasz Myrta <jasiek@klaster.net>
	message dated "Wed, 23 Oct 2002 12:07:09 +0200"
Date: Wed, 15 Jan 2003 14:45:06 -0500
Message-ID: <19505.1042659906@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

[ followup on a gripe from October ]

Tomasz Myrta <jasiek@klaster.net> writes:
> I want to perform query looking like this:

> select
>   user_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 using (group_id)
> where group_id=3;

> This query works, but very slow. Subquery with aggregate is performed 
> for all table rows instead of group_id=3.

Just FYI, this problem is fixed as of CVS tip.  Using an empty table
with an index on group_id, I get a plan like so:

 Nested Loop  (cost=17.10..34.21 rows=1 width=36)
   ->  Subquery Scan x  (cost=17.10..17.11 rows=1 width=12)
         ->  HashAggregate  (cost=17.10..17.11 rows=1 width=12)
               ->  Index Scan using users_group_id on users  (cost=0.00..17.07 rows=5 width=12)
                     Index Cond: (3 = group_id)
   ->  Index Scan using users_group_id on users  (cost=0.00..17.08 rows=1 width=16)
         Index Cond: (users.group_id = "outer".group_id)
         Filter: (group_id = 3)

Notice the condition on group_id has been propagated into both sides of
the join.

			regards, tom lane