Non-aggregate HAVING

Claim: Performance

A HAVING condition that uses no aggregate tests values each row has before grouping. Filtering on those is the job of WHERE. PostgreSQL moves such a condition to WHERE on its own, so in most queries both forms run the same plan. Writing it in WHERE shows the reader where the filter applies and leaves HAVING for conditions on aggregates.

Example

-- The HAVING condition uses no aggregate
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING department != 'TEMP';

PostgreSQL plans this query as if the condition were in WHERE: the scan of employees filters out 'TEMP' before the rows are grouped.

When the plan differs

PostgreSQL leaves the condition in HAVING when it contains a subquery or calls a volatile function such as random(). The condition then runs once per group, after grouping.

• With a subquery, writing the condition in WHERE lets the planner turn the subquery into a join.
• With a volatile function, the two forms return different results, because WHERE calls the function once per row and HAVING calls it once per group.

How to fix it

Move non-aggregate conditions from HAVING to WHERE:

SELECT department, COUNT(*)
FROM employees
WHERE department != 'TEMP'
GROUP BY department;

Leave a condition that calls a volatile function where it is, unless you mean to change when it runs.

Detection

Query Doctor reports this Finding when a HAVING clause contains no aggregate function calls (COUNT, SUM, AVG, MIN, MAX, ARRAY_AGG, STRING_AGG, BOOL_AND, BOOL_OR, EVERY). Mixed clauses that combine non-aggregate conditions with aggregate functions are not flagged.