Making GROUP BY Fast: The Two Indexes That Make the Difference

A GROUP BY on a small table is instant. A GROUP BY on a 100M-row table can be 30 seconds or 30 milliseconds, depending on the index situation. The factor of 1000 between those is mostly two questions: does an index exist that matches the GROUP BY columns, and is work_mem large enough.

Here is the conversation in detail.

How GROUP BY actually executes

Postgres has two main strategies:

HashAggregate — builds a hash table in memory, one bucket per group. Streams through the input, hashing each row's group key and accumulating. Fast when the number of groups fits in work_mem. Falls back to disk-spilled aggregation when it does not, which is much slower.

GroupAggregate — assumes the input is sorted by the group columns. Reads sequentially, emits a row whenever the group key changes. No hash table; minimal memory. Requires sorted input either from a sort step (which is itself expensive on large data) or from an index that already provides the sort order.

The right plan depends on data shape. The planner picks; you can influence it with indexes and work_mem.

The single most useful index

For GROUP BY customer_id to be fast, an index on customer_id lets Postgres use GroupAggregate without a sort step:

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

Now SELECT customer_id, count(*) FROM orders GROUP BY customer_id can scan the index in order, emitting one count per customer as it goes. No sort, no hash table, minimal memory.

For multi-column GROUP BY, the index column order should match:

CREATE INDEX orders_customer_status_idx ON orders (customer_id, status);

SELECT customer_id, status, count(*)
FROM orders
GROUP BY customer_id, status;

The planner walks the index in order and emits aggregates as it crosses key boundaries. Fast on any size of table.

When the WHERE narrows enough that GROUP BY is cheap

If the query filters down to a small subset before grouping, the GROUP BY runs on the subset:

SELECT customer_id, count(*)
FROM orders
WHERE created_at >= now() - interval '7 days'
GROUP BY customer_id;

For this query, the most useful index is one that supports the WHERE filter:

CREATE INDEX orders_created_at_customer
  ON orders (created_at, customer_id);

The leading column matches the WHERE; the second column is the GROUP BY key. Postgres can do an index range scan on created_at, then HashAggregate on customer_id over the small result.

The order of columns in the index matters: leading column for filtering, trailing for grouping or sorting. Reverse the order and the index does not help the WHERE.

work_mem is the other half

HashAggregate needs to fit the hash table in work_mem. If work_mem is 4MB (the default) and your aggregation has 10 million distinct groups, the hash table does not fit. Postgres spills to disk, runs much slower.

For known heavy aggregations, raise work_mem for the session:

SET work_mem = '256MB';
SELECT customer_id, count(*) FROM orders GROUP BY customer_id;
RESET work_mem;

Do this per-session, not globally. work_mem is per-operation, and Postgres can allocate multiple at once. A global setting of 256MB on a busy server with 100 concurrent queries can use 25GB of RAM.

For analytical workloads on dedicated replicas, a higher global work_mem is fine. For OLTP, raise per-session for the queries that need it.

Aggregate-friendly schema choices

A few schema decisions that make GROUP BY easier downstream:

• Pre-aggregate hot dimensions. If the application constantly groups by (tenant_id, day), a materialized view or a generated day column with an index is much cheaper than computing on the fly.
• Use the right type for grouping columns. Grouping on a TEXT column with a long string is slower than on an INTEGER. If group keys are conceptually integers, store them as integers.
• Avoid grouping on expressions. GROUP BY date_trunc('month', created_at) requires either an expression index or a generated column to be cheap. The most reliable shape is a generated column.

A worked example

Real scenario: 50M-row events table, query is "events per tenant per day for the last 30 days," running every minute on a dashboard.

The naive query:

SELECT
  tenant_id,
  date_trunc('day', created_at) AS day,
  count(*)
FROM events
WHERE created_at >= now() - interval '30 days'
GROUP BY tenant_id, date_trunc('day', created_at)
ORDER BY day DESC;

This ran for 12 seconds.

The fix in three steps:

1. Add a generated day column:

ALTER TABLE events
  ADD COLUMN day DATE
  GENERATED ALWAYS AS (created_at::DATE) STORED;

2. Add a composite index:

CREATE INDEX CONCURRENTLY events_day_tenant ON events (day DESC, tenant_id);

3. Rewrite the query to match:

SELECT tenant_id, day, count(*)
FROM events
WHERE day >= current_date - interval '30 days'
GROUP BY tenant_id, day
ORDER BY day DESC;

New runtime: 80 milliseconds. The plan went from "sort 50M rows on the expression" to "index scan the last 30 days, GroupAggregate by index order."

What I check first

When a GROUP BY is slow:

1. EXPLAIN ANALYZE. Find the aggregation node — is it Hash or Group?
2. If Hash and spilling to disk, raise work_mem for the session and re-run.
3. If sorting before group, find an index that gives the right order without the sort.
4. If neither helps, the issue is data volume — the query is doing the work it has to do, and the answer is to materialize.

Most slow GROUP BY queries are fixable in one of those three ways. The remaining cases are usually about pre-aggregation, which is a different conversation.