Aggregate Query Optimization

Practical Guidance for Faster Aggregate Queries

Aggregate queries (GROUP BY, HAVING, and their extensions) can become expensive on large tables because they must touch every relevant row before producing a single group's answer. A handful of practical habits keep them fast.

Running Example

All examples in this chapter use one small orders table:

sql
CREATE TABLE orders (
    id      INT PRIMARY KEY,
    region  VARCHAR(10),
    product VARCHAR(10),
    amount  NUMERIC(8,2)
);

Total of all 8 rows: 1060. Per region: East = 310, West = 550, North = 200. Per product: Widget = 530, Gadget = 530 (100+200+50+120+60 and 150+300+80).

1. Filter with WHERE Before Grouping, Whenever Possible

As established in Topic 15.3, WHERE runs before GROUP BY and eliminates rows that never need to be aggregated at all:

sql
-- Better: WHERE removes non-West rows before any grouping/aggregation work happens
SELECT region, SUM(amount) FROM orders WHERE region = 'West' GROUP BY region;

-- Worse: aggregates every region, then throws away all but West's group
SELECT region, SUM(amount) FROM orders GROUP BY region HAVING region = 'West';

Any condition expressible on raw row values belongs in WHERE. Reserve HAVING strictly for conditions that genuinely require an aggregate result (e.g., HAVING SUM(amount) > 400).

2. Index the GROUP BY / WHERE Columns

An index on region (or a composite index on (region, product) matching your GROUP BY column order) can let the database engine either:

• Use an index scan to quickly find and skip to the rows for a WHERE region = 'West' filter, or
• In some engines, use an ordered index to feed rows into grouping already pre-sorted, avoiding an explicit sort/hash step (a "streaming" aggregate).

Without an index, the engine typically must do a full table scan (reading every row) and then sort or hash all matching rows into groups.

3. Aggregate Over Fewer, Narrower Columns

SELECT region, SUM(amount) FROM orders GROUP BY region only needs to read region and amount from each row. A query that instead does SELECT * , SUM(amount) OVER (...) FROM orders or otherwise drags every column along costs more I/O and more memory per group, purely from carrying unnecessary data through the aggregation step. Select only the columns you actually need.

4. Prefer One GROUPING SETS/ROLLUP/CUBE Query Over Several Separate Ones

As shown in Topics 15.6–15.8, computing multiple grouping levels (by region, by product, grand total) via GROUPING SETS/ROLLUP/CUBE lets the engine read the base table once and derive all requested subtotal levels from that single pass. Running three or four separate GROUP BY queries and combining them with UNION ALL instead re-scans (and often re-sorts) the base table once per query — for a large orders table, this difference is the gap between one full scan and several.

sql
-- One logical scan for 3 grouping levels:
SELECT region, product, SUM(amount)
FROM orders
GROUP BY GROUPING SETS ((region), (product), ());

-- vs. three scans, manually UNIONed — same result, more I/O:
SELECT region, NULL, SUM(amount) FROM orders GROUP BY region
UNION ALL
SELECT NULL, product, SUM(amount) FROM orders GROUP BY product
UNION ALL
SELECT NULL, NULL, SUM(amount) FROM orders;

5. Watch Out for CUBE's Combinatorial Growth

Because CUBE(a, b, c, ...) produces 2^n grouping sets (Topic 15.8), adding more columns to a CUBE can quickly multiply the number of groups the engine must materialize. On a genuinely large table, an unrestrained CUBE over many columns can be far more expensive than a targeted GROUPING SETS list containing only the specific combinations you actually need.

Edge Cases

• An index on the WHERE column doesn't automatically help GROUP BY unless the same (or a covering composite) index also matches the grouping column order.
• HAVING conditions can never be sped up by an index, since they run against already-aggregated group results, not raw table rows.
• Adding a LIMIT after GROUP BY/HAVING does not reduce the aggregation work itself — the engine still must compute (or at least partially compute) all groups before it knows which ones to keep, in the general case.

Key Takeaways / Interview Q&A

• Q: Why is WHERE generally more efficient than an equivalent HAVING condition on a raw column?

A: WHERE discards rows before grouping/aggregation happens, so less data reaches the more expensive grouping step; HAVING discards whole groups after everything has already been aggregated.

• Q: What kind of index helps a GROUP BY query?

A: An index on the grouping column(s) (ideally matching their order), letting the engine scan pre-sorted or quickly skip to relevant rows instead of a full table scan plus sort/hash.

• Q: Why prefer GROUPING SETS over several separate GROUP BY queries UNIONed together?

A: One GROUPING SETS query can compute all requested grouping levels from a single logical scan of the base table, instead of one scan per separate query.

• Q: What's the risk with CUBE on wide grouping-column lists?

A: The number of grouping sets grows as 2^n, so CUBE over many columns can generate and aggregate far more groups than are actually needed.