I have a PostgreSQL database (9.2) with a table of parent-child relations. I have a query that looks for nodes having multiple parents.

The following query works and returns the correct results:

SELECT node,parents FROM
  SELECT nr.child AS node, COUNT(nr.parent) AS parents 
  FROM node_relation nr 
  GROUP BY nr.child
) AS count WHERE parents > 1;

The result set:

 node   | parents
 n21174 |       2
 n8635  |       2
(2 rows)

The table definition is:

            Table "public.node_relation"
   Column    |         Type          |   Modifiers
 child       | character varying(50) | not null
 parent      | character varying(50) | not null
    "node_relation_pkey" PRIMARY KEY, btree (child, parent)

I re-wrote the query to not use a sub-select:

SELECT child AS node, COUNT(parent) AS parents 
FROM node_relation 
GROUP BY child 
HAVING COUNT(parent) > 1;

The new query works, but I wonder about the COUNT function being invoked multiple times.

Update: Here is the query plan:

                                                 QUERY PLAN
 GroupAggregate  (cost=0.00..1658.81 rows=19970 width=16)
   Filter: (count(parent) > 1)
   ->  Index Only Scan using node_relation_pkey on node_relation  (cost=0.00..1259.40 rows=19971 width=16)

I would prefer to use the parents alias but the following does not work:

SELECT child AS node, COUNT(parent) AS parents 
FROM node_relation 
GROUP BY child 
HAVING parents > 1;

ERROR:  column "parents" does not exist
LINE 1: ...parents FROM node_relation GROUP BY child HAVING parents > ...

Will PostgreSQL optimize out the multiple invocations of COUNT?

If not, is there an alternative form of this query that would be more efficient?

  • 3
    Yes, Postgres is smart enough to not calculate the COUNT() twice. Commented Oct 29, 2015 at 16:50
  • 4
    Don't forget order of operations : FROM clause, WHERE clause, GROUP BY clause, HAVING clause, SELECT clause, ORDER BY clause Commented Oct 29, 2015 at 16:52
  • 1
    @CraigEfrein this is just the logical order of operations. Not necessarily the path of the actual execution that the optimizer chooses. I might be wrong but I think Postgres optimizer will handle the 2 queries identically. Commented Oct 29, 2015 at 16:54
  • 1
    @ypercube, I mentioned the order because he is referring to parents as a column alias where as it doesn't even exist when the having is parsed Commented Oct 29, 2015 at 16:55
  • 1
    Ah - I think I understand your question better now. The HAVING clause will re-use the same values from the SELECT clause.. That's why it's a HAVING (vs a WHERE).. Yes, Postgres is smart enough to do that (as ypercube said) Commented Oct 29, 2015 at 18:53

1 Answer 1


Your second query (the one where you implement it with the HAVING clause) is probably faster. In your first query (with the subselect), postgres has to calculate the count values for the entire table. In your second query, it can start ignoring rows to count once it reaches a count value above 1 (although I don't 100% know if postgres is smart enough to do that - I'm fairly certain it is, though).

Since COUNT() is an aggregate function, it's going to be run the number of times it'll be run regardless of the number of rows returned. If you had a function that was NOT an aggregate function, then running your group and where/having clause in a subselect would likely be faster.

Example of what I'm referring to:

  some_non_agg_function(a.id, a.child)
FROM join_tab1 a
GROUP BY a.id, a.child
HAVING COUNT(a.id) > 1;
-- probably not as fast as
WITH rows_to_process AS (
    id, child
  FROM join_tab1 a
  GROUP BY a.id, a.child
  HAVING COUNT(a.id) > 1
  some_non_agg_function(id, child)
FROM rows_to_process;

To answer your question specifically - yes, postgres will keep track of what aggregate values it has calculated and re-use them (as opposed to re-calculating them) within the HAVING clause. I believe it will also re-use them in the SELECT clause (if for some weird reason you run the exact same aggregate more than once in the SELECT)

To quote Postgres's excellent documentation (bold mine)

It is important to understand the interaction between aggregates and SQL's WHERE and HAVING clauses. The fundamental difference between WHERE and HAVING is this: WHERE selects input rows before groups and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereas HAVING selects group rows after groups and aggregates are computed. Thus, the WHERE clause must not contain aggregate functions; it makes no sense to try to use an aggregate to determine which rows will be inputs to the aggregates. On the other hand, the HAVING clause always contains aggregate functions. (Strictly speaking, you are allowed to write a HAVING clause that doesn't use aggregates, but it's seldom useful. The same condition could be used more efficiently at the WHERE stage.)

This doesn't specifically say that it re-uses the calculated values .. but it implies it with saying that the HAVING clause is used after aggregates are calculated.

  • Interesting point about possibly ignoring rows at query time based on the current value of the count, because the other part of the query requires the full count to be returned in the result set. If it is optimized out, then the operation really is being performed multiple times (unless a shared counter is used).
    – Parker
    Commented Oct 29, 2015 at 17:13
  • 1
    Notice that my answer addresses your concern tangentially, since my answer (mostly) discusses how to optimize if the function is NOT an aggregate function. With regard to an aggregate function, postgres will keep track of what aggregate values it has calculated and will re-use the calculations (if available) in the HAVING clause (vs re-calculating them). Commented Oct 29, 2015 at 18:55
  • I appreciate the answer, and I'll accept it as soon as I can find a reference to confirm this behavior. I'm digging through the postgres documentation but haven't found anything relevant yet.
    – Parker
    Commented Oct 29, 2015 at 19:06
  • 2
    You can see it if you run an explain on the query - you will see that postgres only runs the COUNT() aggregation once. :) Commented Oct 29, 2015 at 19:10
  • Done. See updated question. I believe that answers it.
    – Parker
    Commented Oct 29, 2015 at 19:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.