How do you optimize slow aggregates on a large table?

Question

IT Interview Practice · Accepted Answer

To optimize a slow aggregate, first identify **what the result means and which operation consumes the time**. An aggregate combines rows into a summary, such as revenue per merchant. Its cost depends on how much data must be read and combined.

Consider a dashboard showing last month's revenue. Use this example to decide whether to improve the query, store a prepared summary or move reporting elsewhere.

## 1. Confirm the answer before making it faster

Clarify whether revenue means paid or created orders, how refunds are handled and which currencies are included. Also ask how fresh the dashboard must be. These choices change both the query and the available optimizations.

A common mistake is joining a payment to several order items and then summing the payment amount:

```text
[Payment: 100] -> [Join with 3 items] -> [100, 100, 100]
                                               |
                                          [SUM = 300]
```

The numbers are illustrative. The query has multiplied the payment, so speeding it up would only return an incorrect total faster. Aggregate at the intended business level or change the join before evaluating performance.

## 2. Find the expensive part of the execution

An execution plan shows the operations used to obtain the result. Check the expensive steps and their input sizes, rather than assuming the table simply needs another index.

| Observation | Question to investigate |
| --- | --- |
| Many irrelevant rows are read | Can filters or partition pruning exclude them? |
| Actual row counts differ greatly from estimates | Are statistics representative of this data? |
| A join repeats work many times | Is its input too large or its access path unsuitable? |
| Grouping or sorting writes temporary data to disk | Can the input be reduced before changing memory limits? |

In PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) executes the query and reports runtime information. Run an expensive investigation in a controlled environment or with a deliberate production budget. [PostgreSQL EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html).

## 3. Reduce work in the current database

For the dashboard, select the required month and columns. Check whether the plan actually skips unrelated partitions and whether joins bring in data that the summary does not need.

An index is useful when it avoids enough reading. If the report needs most rows, a sequential scan may be appropriate. Judge the resulting plan and measured cost, not merely whether an index appears.

If sorting spills to disk, reducing its input may solve the problem. Raising memory for every query can instead exhaust RAM when many reports run together. Test under representative concurrent load.

## 4. Decide whether to prepare the result in advance

If many users request the same totals and a delay is acceptable, repeatedly scanning raw orders wastes work. A summary table can store totals by day, merchant and currency; the dashboard combines these smaller results.

```text
[Orders] -> [Scheduled / incremental calculation]
                              |
                       [Daily summaries] -> [Dashboard]
```

The benefit comes from doing shared calculations once instead of on every request. A materialized view is another way to store a query result, with its own refresh behavior.

That saving introduces responsibilities:

- Define how often results refresh and show the user the relevant freshness.
- Apply refunds, late orders and corrections without double counting.
- Make repeated processing safe and retain a way to rebuild summaries.
- Store sum and count when combining averages; averaging averages can be wrong.

For this dashboard, prepared summaries are a candidate only if their freshness and correction rules meet the requirement from step one.

## 5. Separate reporting when measurements justify it

If broad analytical scans remain expensive and interfere with purchases, evaluate an analytical store suited to that workload. Include synchronization delay, recovery and the cost of operating another system.

A read replica may isolate reporting load, but the same query can still be expensive there. Separation changes where the cost is paid; it does not necessarily remove it.

Compare each change against a trusted result and the same test load. Check response time, resources, freshness and impact on writes.

**Remember:** correct result → measured bottleneck → less unnecessary work → prepared summaries if suitable → separate analytics if still needed.

Observation	Question to investigate
Many irrelevant rows are read	Can filters or partition pruning exclude them?
Actual row counts differ greatly from estimates	Are statistics representative of this data?
A join repeats work many times	Is its input too large or its access path unsuitable?
Grouping or sorting writes temporary data to disk	Can the input be reduced before changing memory limits?

1. Confirm the answer before making it faster

2. Find the expensive part of the execution

3. Reduce work in the current database

4. Decide whether to prepare the result in advance

5. Separate reporting when measurements justify it

Related questions