From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: sub-select with aggregate
Date: Wed, 23 Oct 2002 07:25:53 -0700 (PDT)
Message-ID: <20021023071418.N4419-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <3DB6AC12.3030506@klaster.net>
On Wed, 23 Oct 2002, Tomasz Myrta wrote:
> Uz.ytkownik Stephan Szabo napisa?:
> > Does using X.group_id=3 in the where clause work better?
> It works better, but not if you want to create a view and make
> "select * from some_view where group_id=3" :-(
But you can't do that anyway, because you don't expose group_id
in the original query. I assume user_id was a mistake then and was
meant to be group_id or that both were meant to be in the
select list.
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);
In the first case changing the order means that the output
group_id column is X.group_id rather than users.group_id
(using removes one of them which is why group_id isn't
ambiguous. In the second it uses on to get both group_ids
and exposes the one from X.
> > 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.
> Do you mean the second query will work on 7.3?
No, the case of setting X.group_id.
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: sszabo@megazone23.bigpanda.com, jasiek@klaster.net
Subject: Re: sub-select with aggregate
In-Reply-To: <20021023071418.N4419-100000@megazone23.bigpanda.com>
* 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