Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 4EBCA47650D
	for <pgsql-sql@postgresql.org>; Wed, 23 Oct 2002 09:02:47 -0400 (EDT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id BF9864764CF
	for <pgsql-sql@postgresql.org>; Wed, 23 Oct 2002 09:02:46 -0400 (EDT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 4775AD60B; Wed, 23 Oct 2002 06:02:50 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 28F785C02; Wed, 23 Oct 2002 06:02:50 -0700 (PDT)
Date: Wed, 23 Oct 2002 06:02:49 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: sub-select with aggregate
In-Reply-To: <3DB674CD.50404@klaster.net>
Message-ID: <20021023055926.O3876-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200210/291
X-Sequence-Number: 10008

On Wed, 23 Oct 2002, Tomasz Myrta wrote:

> Hi
> 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.

Does using X.group_id=3 in the where clause work better?

On 7.3 with no statistics for the table, that appears
to move the filter into the subquery plan rather than the
outer users scan.