From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: sub-select with aggregate
Date: Wed, 15 Jan 2003 14:45:06 -0500
Message-ID: <19505.1042659906@sss.pgh.pa.us> (raw)
In-Reply-To: <3DB674CD.50404@klaster.net>
References: <3DB674CD.50404@klaster.net>
[ 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
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: tgl@sss.pgh.pa.us, jasiek@klaster.net
Subject: Re: sub-select with aggregate
In-Reply-To: <19505.1042659906@sss.pgh.pa.us>
* 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