HAVING vs WHERE: filter before or after grouping

Two filters at two different times

Aggregation has a fixed pipeline, and the two filter clauses sit on opposite sides of the collapse:

1. FROM / JOIN — assemble the raw rows.
2. WHERE — discard rows you don’t want. This runs before grouping, so it works on individual rows and cannot see any aggregate (there are no groups yet).
3. GROUP BY — collapse the surviving rows into buckets.
4. HAVING — discard whole groups you don’t want. This runs after aggregation, so it can reference aggregates like SUM(total) or COUNT(*).
5. SELECT / ORDER BY / LIMIT — shape the output.

Together these steps mean every filter has exactly one right place: if the condition is about a single row it belongs in step 2 (WHERE); if it’s about the result of collapsing a group it belongs in step 4 (HAVING). Putting a row filter in step 4 wastes everything the grouping step had to chew through.

Concretely, the correct report:

SELECT user_id, SUM(total) AS paid_revenue
FROM orders
WHERE status = 'paid'          -- per-row condition → before grouping
GROUP BY user_id
HAVING SUM(total) > 10000;      -- condition on the aggregate → after grouping

status = 'paid' is decidable on a single row, so it goes in WHERE and shrinks the input to GROUP BY. SUM(total) > 10000 can only be evaluated once a whole bucket exists, so it must be HAVING.

Why aggregates are illegal in WHERE

Try moving the threshold into WHERE and Postgres refuses:

SELECT user_id, SUM(total)
FROM orders
WHERE SUM(total) > 10000        -- ERROR: aggregate functions are not allowed in WHERE
GROUP BY user_id;

This isn’t a missing feature — it’s a timing impossibility. WHERE runs before GROUP BY, at a point where no group exists yet, so SUM(total) has nothing to sum over. The aggregate is undefined at that moment. HAVING exists precisely to be the filter that runs after the groups (and their aggregates) materialize.

The performance lever: push filters down

Here’s the part that separates a correct query from a fast one. Both of these return the same rows:

-- SLOW: every order gets grouped, then non-paid groups are... still wrong & costly
SELECT user_id, SUM(total)
FROM orders
GROUP BY user_id
HAVING bool_and(status = 'paid');   -- contorted, and groups ALL orders first

-- FAST: drop non-paid rows before they ever reach the grouping
SELECT user_id, SUM(total)
FROM orders
WHERE status = 'paid'
GROUP BY user_id;

A row filter belongs in WHERE so fewer rows reach the (expensive) grouping step. Grouping has to hash or sort its input; halving the input roughly halves that cost, and if there’s a partial index on status the planner can skip the non-matching rows entirely. Putting a plain per-row condition in HAVING is a code smell: it usually means a filter that should have run before grouping is running after it, doing the full grouping work on rows you were going to throw away.

Put numbers on it. On a 12M-row orders table where only ~1.2M are paid, the HAVING-only version EXPLAIN ANALYZEs as Seq Scan (rows=12000000) feeding a HashAggregate that builds a bucket for every user — 9s wall time, with the aggregate spilling to disk (Disk Usage: 180000 kB) because all 2M user groups had to be materialized. Move status='paid' into WHERE and the plan becomes a Bitmap Index Scan on a partial WHERE status='paid' index returning rows=1200000, feeding a HashAggregate of ~400k surviving-user buckets that now fits work_mem (no Disk line) — ~200ms. Same result rows, but the grouping chewed 10× fewer input rows and built ~5× fewer buckets. The lever isn’t micro-optimization: it’s the difference between a 9s and a 0.2s query.