From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Madison Kelly <linux(at)alteeve(dot)com>, PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: subquery/alias question |
Date: | 2007-09-26 02:26:55 |
Message-ID: | 1AC876D7-C4C5-4EDB-B0B8-A632A8243415@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sep 25, 2007, at 17:30 , Alvaro Herrera wrote:
> Michael Glaesemann wrote:
>>
>> select dom_id,
>> dom_name,
>> usr_count
>> from domains
>> natural join (select usr_dom_id as dom_id,
>> count(usr_dom_id) as usr_count
>> from users) u
>> where usr_count > 0
>> order by dom_name;
>
> Maybe the usr_count should be tested in a HAVING clause instead of
> WHERE? And put the count(*) in the result list instead of a
> subselect.
> That feels more natural to me anyway.
I believe you'd have to write it like
select dom_id, dom_name, count(usr_dom_id) as usr_count
from domains
join users on (usr_dom_id = dom_id)
having count(usr_dom_id) > 0
order by dom_name;
I don't know how the performance would compare. I think the backend
is smart enough to know it doesn't need to perform two seq scans to
calculate count(usr_dom_id), but I wasn't sure.
Madison, how do the two queries compare with explain analyze?
Michael Glaesemann
grzm seespotcode net
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-09-26 02:44:40 | Re: subquery/alias question |
Previous Message | paul.dorman | 2007-09-26 02:03:17 | DAGs and recursive queries |