Skip site navigation (1) Skip section navigation (2)

Re: subquery/alias question

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 (view raw or flat)
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



In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2007-09-26 02:44:40
Subject: Re: subquery/alias question
Previous:From: paul.dormanDate: 2007-09-26 02:03:17
Subject: DAGs and recursive queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group