Aggregate Performance

Hook: Interviewers love this question because real databases rarely fail on syntax; they fail when a harmless-looking summary suddenly scans millions of rows.

Question: What does aggregate performance mean in SQL, and how do you make GROUP BY and summary queries faster?

Answer: Aggregate performance is about how quickly SQL can compute totals, counts, averages, and grouped summaries. The biggest wins usually come from reducing rows early with WHERE, grouping on a useful index or partition key, and avoiding repeated scans of the same large table. If the same summary is read often, precomputing it in a summary table or materialized view is often faster than recalculating it every time.

Interview-Ready Answer: I think about aggregate performance as a question of how many rows the database has to touch and whether it can keep the grouping in memory. My first move is to filter early with WHERE, because HAVING does not reduce input before grouping. Then I look for an index or partition key that matches the filter or group key, and if the same summary is queried often, I precompute it with a summary table or materialized view so the database does less work at read time.

What aggregate performance means

In SQL, an aggregate is a function that rolls many rows into a smaller result, like COUNT, SUM, AVG, MIN, or MAX. Performance matters because the database may need to read every matching row, build groups, and sometimes sort or hash those groups. The key idea is simple: fewer rows in, less work out.

How the engine usually works

1. Filter first. The WHERE clause removes rows before aggregation starts, so it is the best place to cut work.
2. Choose a grouping strategy. The planner often picks either a hash aggregate or a sort aggregate. A hash aggregate is a grouping method that keeps one bucket per group in memory; a sort aggregate groups rows after they are ordered by the grouping key.
3. Build the groups. For each row, the engine updates counters, sums, or min/max values inside the matching group.
4. Spill if memory is too small. If the working set does not fit in memory, the database writes temporary data to disk, which is much slower than RAM.
5. Return the final rows. The engine emits one row per group, or one row for a whole-table aggregate.

Hash aggregate vs sort aggregate

When and why to use each approach

• Use plain aggregation when the question changes often and the table is small enough to scan quickly.
• Use indexes or partition pruning when your filter matches the data layout, such as recent dates or a tenant id. Cardinality means the number of distinct values; high-cardinality grouping keys can create many buckets and use more memory.
• Use a summary table or materialized view when users ask for the same totals over and over, such as daily revenue or weekly active users.

Performance and complexity notes

• A full scan is usually O(n) because the engine must look at each matching row.
• Hash aggregation is usually close to O(n) time, with memory roughly tied to the number of groups.
• Sort aggregation is often O(n log n) if a sort is required.
• In PostgreSQL, work_mem defaults to 4MB; each sort or hash step gets its own budget, so complex queries can spill sooner than you expect.
• COUNT(*) still has to visit every visible row, COUNT(column) ignores NULL, and COUNT(DISTINCT ...) is usually more expensive because the engine must deduplicate values.

Important edge cases

• SUM returns NULL when there are no matching non-NULL rows, so use COALESCE if you need zero.
• HAVING filters after grouping, so it does not save the cost of grouping rows you later discard.
• Grouping by a unique id often creates one group per row, which can be slower and less useful than filtering first.

Real-world story

Imagine a checkout service for an e-commerce site. The product team wants a dashboard showing revenue by day and customer segment. A developer writes a query that groups the entire orders table every time the dashboard loads. It works on a test database, but in production the table has hundreds of millions of rows, so the query spills to temp files, the page takes seconds to load, and the database CPU spikes.

What the team sees: slow dashboard requests, temp file growth, and sometimes log lines about hash aggregation or disk spill. Users complain that charts are blank or lag behind by minutes. The fix is to add a date filter, make sure the filter and group keys are index-friendly, and move the most common totals into a daily summary table or materialized view. That turns a repeated expensive scan into a cheap lookup.

Follow-up & Tricky Questions:

• How do WHERE and HAVING differ for performance? WHERE removes rows before grouping, so it reduces work. HAVING filters after aggregation, so it cannot save the cost of building groups you later discard.
• When does an index help a GROUP BY? It helps most when the filter or group key matches the index order, or when the database can read rows already sorted. That can reduce both the number of rows scanned and the cost of sorting.
• Hash aggregate vs sort aggregate? Hash aggregate is usually faster on unsorted input, but it needs memory for the hash table and may spill. Sort aggregate is better when the input is already ordered or the planner can reuse an index order.
• Why is COUNT(DISTINCT ...) slower? The engine must deduplicate values, which means extra memory, extra sorting, or both. It is much closer to building a set than to simply counting rows.
• When should I precompute aggregates? When the same summary is read often and the base table changes less frequently than it is queried. The tradeoff is extra write cost and refresh logic, but the read path becomes much cheaper.
• Tricky: Does SUM return 0 on no rows? No. SUM returns NULL when there are no matching non-NULL values, so use COALESCE if the business wants zero.
• Tricky: Can HAVING replace WHERE? Not for row reduction. HAVING runs after grouping, so it is usually slower for simple filters and should not be used as a substitute for WHERE.
• Tricky: Can COUNT(*) avoid touching every row? Sometimes a planner can use an index-oriented path for a narrow filtered query, but there is no magic shortcut for the general case; the engine still has to account for all qualifying visible rows.

Common Mistakes:

• Filtering in HAVING instead of WHERE. Correction: use WHERE whenever the condition does not depend on the aggregate result.
• Grouping on a very high-cardinality column without need. Correction: group on the smallest key that answers the question, or pre-aggregate first.
• Forgetting NULL behavior. Correction: remember that COUNT(column) skips NULL and SUM can return NULL on empty input.
• Running the same expensive report on every request. Correction: cache it in a summary table or materialized view when the business can tolerate refresh lag.

Memory Hook: Filter first, group second, sort last — like cleaning a pantry: throw away junk before you count jars, and only line up the jars if you truly need to.

Cheat Sheet:

• WHERE shrinks input; HAVING trims grouped output.
• Hash aggregate is usually the fast default on unsorted data.
• Sort aggregate wins when the data already arrives in order.
• Indexes help most when they match the filter or grouping pattern.
• Watch for spills: too many groups or too little memory slows everything down.
• Use COALESCE when empty aggregates must show zero instead of NULL.

Practice Tasks:

• Write a query that returns daily order count and revenue for the last 7 days.
• Compare COUNT(*) and COUNT(amount) after inserting a few NULL values.
• Add an index on (created_at, customer_id), run EXPLAIN, and see whether the plan changes.