Re: subquery/alias question

Michael Glaesemann <grzm@seespotcode.net> writes:
> 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.

It has been smart enough for a few years now --- don't recall when
exactly, but nodeAgg.c quoth

     * Perform lookups of aggregate function info, and initialize the
     * unchanging fields of the per-agg data.  We also detect duplicate
     * aggregates (for example, "SELECT sum(x) ... HAVING sum(x) > 0"). When
     * duplicates are detected, we only make an AggStatePerAgg struct for the
     * first one.  The clones are simply pointed at the same result entry by
     * giving them duplicate aggno values.

... which in English means we just do the calculation once ...

            regards, tom lane