HardSQL#808 min readJul 11, 2026

Aggregate Optimization

practiceperformancelearning

Practice modeTest yourself instead of reading straight through

Think of this like sorting mail into bins before counting envelopes — interviewers love it because it reveals whether you know how databases avoid doing extra work.

Question: What is aggregate optimization in SQL?

Answer: Aggregate optimization is the set of tricks a database uses to make COUNT, SUM, AVG, MIN, MAX, and GROUP BY queries run faster. The engine tries to reduce rows early, choose the cheapest aggregate plan, and avoid extra sorting or disk spills. In practice, that means filtering before grouping, using helpful indexes, and sometimes precomputing totals in summary tables.

Interview-Ready Answer: In SQL, aggregate optimization means helping the database compute grouped results with less work. I would explain it as: first cut down the rows with WHERE, then let the optimizer pick the best physical strategy such as hash aggregate or stream aggregate, and if the query is repeated a lot, pre-aggregate into a summary table or materialized view. The biggest gotcha is memory: if a hash aggregate has too many groups, it can spill to disk and get much slower.

Memory map — visual summary of this topic — 🧠 Memory Map

What aggregate optimization really means

Aggregation turns many rows into fewer rows. A query like SELECT region, SUM(amount) FROM sales GROUP BY region looks simple, but the database still has to read rows, build groups, keep running totals, and then return one row per group. Optimization is about doing those steps with the least CPU, memory, and disk I/O possible.

How the engine works under the hood

Apply WHERE first. Rows filtered out here never reach the aggregate. This is the biggest win when you only need a date range or a subset of customers.
Read only the needed columns. If the query only needs region and amount, the engine tries not to touch extra columns. Less data read means fewer page reads and less memory traffic.
Choose a physical aggregate operator. A physical operator is the actual execution strategy the engine uses, such as hashing or sorting.
Maintain one state per group. For each key, the engine stores running values like count, sum, and min. A hash table is often used here: it is a key-to-bucket structure that gives quick lookup for each group.
Finalize the results. AVG becomes SUM / COUNT, COUNT(*) becomes a row total, and MIN/MAX are read from the stored state.
Merge partial results when parallelism is used. A partial aggregate is a first pass done by a worker thread; the final aggregate combines those smaller results. This reduces the amount of data that must be moved around.

Hash vs sort vs pre-aggregation

Plan	Best when	Strength	Risk
Hash Aggregate	Input is unsorted	Fast per group	Spills when memory is small
Sort / Stream Aggregate	Input is already ordered	Low memory use	Sort cost can be high
Pre-aggregation	Query repeats often	Very fast reads	Data can become stale

If the engine can read rows in group-key order, it can do a stream aggregate: it finishes one group, emits the result, then moves to the next. That is cheaper than building a huge in-memory hash table. If rows are not ordered, hashing is often faster than sorting, because it avoids O(n log n) sort work. But if the number of distinct groups is huge, the hash table can grow large and spill to disk, which hurts latency a lot.

When and why to use each optimization

Filter early: Add selective WHERE clauses so the aggregate sees fewer rows.
Index the filter and group keys: A covering index is an index that contains all columns needed by the query, so the engine may avoid extra table lookups.
Precompute hot metrics: Use summary tables or materialized views when dashboards ask the same totals all day long.
Partition by time: Time-based partitions let the engine skip whole chunks of data for date-range reports.
Use approximate methods only when allowed: They trade a small error for speed, which is fine for analytics but not for billing.

Performance notes interviewers like

Scanning n rows is still at least O(n); optimization mostly reduces constants, memory pressure, and spill risk.
Hash aggregation is usually O(n) average time with O(g) memory, where g is the number of groups.
Sort-based aggregation is closer to O(n log n) because of the sort, but it can be better if the input is already ordered.
In PostgreSQL, a hash aggregate can spill when the working memory is too small; the real-world symptom is temp file growth and a query that used to finish in under a second suddenly taking many seconds or timing out.

Important edge cases

COUNT(*) counts rows, but COUNT(column) ignores NULL values.
SUM(column) over no matching rows returns NULL, so reports often need COALESCE(SUM(...), 0).
DISTINCT inside an aggregate usually adds extra dedup work, so it is more expensive than a plain aggregate.
Very high-cardinality group keys can make hash aggregation memory-heavy, even if the query is logically simple.

Memory hook: Sort the coins into piles first, then total each pile. That picture reminds you to filter early, group by a key, and precompute only when the same totals are asked for repeatedly.

Real-world story

A checkout service at an e-commerce company shows a live dashboard of revenue by day and region. The raw orders table has hundreds of millions of rows, so every second matters. The team starts with a plain GROUP BY on the fact table, but the dashboard gets slower every week as data grows. The fix is to filter to the last 30 days, partition by date, and keep a daily summary table for the most common metrics.

What goes wrong when people misunderstand it

A developer changes the dashboard query to add COUNT(DISTINCT customer_id) and removes the date filter because they want a lifetime number. Suddenly the hash table explodes, temp disk usage jumps, and the query times out during peak traffic. Users see blank charts, logs show slow queries and temp-file growth, and the BI team blames the app when the real problem is that the aggregate is doing far too much work on too many rows.

SQL

-- Demo: basic aggregate optimization ideas in one runnable script.
-- The data set is small, but the patterns scale to large tables.

DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  order_date DATE NOT NULL,
  region VARCHAR(20) NOT NULL,
  amount DECIMAL(10,2)
);

INSERT INTO sales (sale_id, order_date, region, amount) VALUES
  (1, '2024-01-01', 'East', 100.00),
  (2, '2024-01-01', 'East', 50.00),
  (3, '2024-01-01', 'West', 75.00),
  (4, '2024-01-02', 'East', NULL),
  (5, '2024-01-02', 'West', 25.00),
  (6, '2024-01-02', 'West', 25.00);

-- Basic grouped aggregate: one row per region.
SELECT
  region,
  COUNT(*) AS rows_seen,
  COUNT(amount) AS paid_rows,
  SUM(amount) AS total_amount
FROM sales
GROUP BY region
ORDER BY region;

-- Edge case: COUNT(amount) skips NULL, but COUNT(*) still counts the row.
-- This is why row counts and non-null counts are not interchangeable.
SELECT
  order_date,
  COUNT(*) AS total_rows,
  COUNT(amount) AS non_null_amount_rows,
  COALESCE(SUM(amount), 0) AS safe_total
FROM sales
GROUP BY order_date
ORDER BY order_date;

-- Edge case: no matching rows produce NULL from SUM, so reports often wrap COALESCE.
SELECT COALESCE(SUM(amount), 0) AS missing_region_total
FROM sales
WHERE region = 'North';

-- Pre-aggregation pattern for dashboards: compute the summary once, query it many times.
DROP TABLE IF EXISTS daily_sales_summary;
CREATE TABLE daily_sales_summary AS
SELECT
  order_date,
  region,
  COUNT(*) AS orders,
  COALESCE(SUM(amount), 0) AS revenue
FROM sales
GROUP BY order_date, region;

SELECT *
FROM daily_sales_summary
ORDER BY order_date, region;

Follow-up & Tricky Questions:

How does the optimizer choose hash aggregate vs sort aggregate? It compares estimated row counts, number of groups, available memory, and whether the input is already ordered. If the rows arrive in group-key order, a stream-style plan can be very cheap; otherwise hashing often wins unless memory is tight.
When does an index help a GROUP BY? An index helps most when it supports the filter and also provides rows in the grouping order. That can let the engine read fewer pages and avoid a separate sort.
Why is COUNT(*) often faster than COUNT(column)? COUNT(*) only needs to count rows, while COUNT(column) must check the column for NULL. The difference is small on tiny data, but it matters at scale.
Why are DISTINCT aggregates expensive? The engine must remove duplicates before producing the final answer, which adds memory or sort work. COUNT(DISTINCT user_id) is usually much heavier than COUNT(*).
When should I use a materialized view or summary table? Use one when the same aggregate is read repeatedly and freshness can lag a bit. If the metric is hot and expensive, precomputing it is often the best optimization.
Tricky: Does SUM return 0 when no rows match? No. It returns NULL, so you need COALESCE if the report expects zero.
Tricky: Does COUNT(column) count rows with NULL in that column? No. It skips them, which makes it different from COUNT(*).
Tricky: Does ordering the final output automatically make aggregation faster? Not by itself. It only helps if the input is already ordered on the grouping key, because then the engine can stream the groups without a separate sort.

Common Mistakes:

Filtering too late. WHERE should remove rows before GROUP BY; otherwise the engine groups data it did not need.
Confusing COUNT(*) and COUNT(col). Use COUNT(*) for rows and COUNT(col) only when you intentionally want to ignore NULL.
Ignoring spill risk. A large number of groups can push hash aggregation to disk; watch memory settings and temp-file usage.
Precomputing everything. Summary tables are great for hot dashboards, but they add refresh cost and can go stale.

Memory Hook: Filter first, then sort into piles, then total the piles. That one line captures the whole mental model: shrink the data, group it cheaply, and precompute only when the same answer is asked again and again.

Cheat Sheet:

Reduce rows early with WHERE.
Use indexes that support filter and group keys.
Prefer hash aggregate for unsorted input, stream aggregate for ordered input.
Watch for spills when the number of groups is large.
Use COALESCE for empty-set totals that should show zero.
Pre-aggregate repeated dashboard queries.

Practice Tasks:

Write a query that returns daily order counts and revenue for the last 7 days only.
Rewrite a COUNT(DISTINCT customer_id) report using a pre-aggregated summary table.
Compare COUNT(*), COUNT(amount), and SUM(amount) on a table that contains NULL values.

Back to Questions←→to navigate

Plan

Best when

Strength

Risk

Hash Aggregate

Input is unsorted

Fast per group

Spills when memory is small

Sort / Stream Aggregate

Input is already ordered

Low memory use

Sort cost can be high

Pre-aggregation

Query repeats often

Very fast reads

Data can become stale

-- Demo: basic aggregate optimization ideas in one runnable script. -- The data set is small, but the patterns scale to large tables. DROP TABLE IF EXISTS sales; CREATE TABLE sales ( sale_id INTEGER PRIMARY KEY, order_date DATE NOT NULL, region VARCHAR(20) NOT NULL, amount DECIMAL(10,2) ); INSERT INTO sales (sale_id, order_date, region, amount) VALUES (1, '2024-01-01', 'East', 100.00), (2, '2024-01-01', 'East', 50.00), (3, '2024-01-01', 'West', 75.00), (4, '2024-01-02', 'East', NULL), (5, '2024-01-02', 'West', 25.00), (6, '2024-01-02', 'West', 25.00); -- Basic grouped aggregate: one row per region. SELECT region, COUNT(*) AS rows_seen, COUNT(amount) AS paid_rows, SUM(amount) AS total_amount FROM sales GROUP BY region ORDER BY region; -- Edge case: COUNT(amount) skips NULL, but COUNT(*) still counts the row. -- This is why row counts and non-null counts are not interchangeable. SELECT order_date, COUNT(*) AS total_rows, COUNT(amount) AS non_null_amount_rows, COALESCE(SUM(amount), 0) AS safe_total FROM sales GROUP BY order_date ORDER BY order_date; -- Edge case: no matching rows produce NULL from SUM, so reports often wrap COALESCE. SELECT COALESCE(SUM(amount), 0) AS missing_region_total FROM sales WHERE region = 'North'; -- Pre-aggregation pattern for dashboards: compute the summary once, query it many times. DROP TABLE IF EXISTS daily_sales_summary; CREATE TABLE daily_sales_summary AS SELECT order_date, region, COUNT(*) AS orders, COALESCE(SUM(amount), 0) AS revenue FROM sales GROUP BY order_date, region; SELECT * FROM daily_sales_summary ORDER BY order_date, region;

Common Mistakes:

Filtering too late. WHERE should remove rows before GROUP BY; otherwise the engine groups data it did not need.
Confusing COUNT(*) and COUNT(col). Use COUNT(*) for rows and COUNT(col) only when you intentionally want to ignore NULL.
Ignoring spill risk. A large number of groups can push hash aggregation to disk; watch memory settings and temp-file usage.
Precomputing everything. Summary tables are great for hot dashboards, but they add refresh cost and can go stale.

Cheat Sheet:

Reduce rows early with WHERE.
Use indexes that support filter and group keys.
Prefer hash aggregate for unsorted input, stream aggregate for ordered input.
Watch for spills when the number of groups is large.
Use COALESCE for empty-set totals that should show zero.
Pre-aggregate repeated dashboard queries.

Practice Tasks:

Write a query that returns daily order counts and revenue for the last 7 days only.
Rewrite a COUNT(DISTINCT customer_id) report using a pre-aggregated summary table.
Compare COUNT(*), COUNT(amount), and SUM(amount) on a table that contains NULL values.