Re: subquery/alias question

Michael Glaesemann <grzm(at)seespotcode(dot)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