Sort & Hash Aggregates

Sorts and hash aggregates need RAM up to work_mem per plan node per operation. When memory is insufficient, PostgreSQL 18.4 spills to disk and runtime jumps.

Recipe

Quick-reference recipe card - copy-paste ready.

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, count(*), sum(total)
FROM orders
GROUP BY customer_id
ORDER BY sum(total) DESC
LIMIT 20;

When to reach for this: Aggregation or ORDER BY queries show high I/O or external merge / Disk in the plan.

Working Example

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  total numeric(12,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
INSERT INTO orders (customer_id, total)
SELECT (random() * 50000)::bigint, (random() * 1000)::numeric(12,2)
FROM generate_series(1, 1000000);
 
ANALYZE orders;
 
SET work_mem = '1MB';
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, sum(total) AS revenue
FROM orders
GROUP BY customer_id
ORDER BY revenue DESC;
 
RESET work_mem;

Look for HashAggregate vs GroupAggregate, and Sort Method: external merge Disk when work_mem is tiny.

What this demonstrates:

Hash aggregate preferred when sorting is not required
Sort spill when work_mem is exceeded
Why OLTP and OLAP roles need different work_mem

Deep Dive

How It Works

HashAggregate - Builds a hash table keyed by GROUP BY columns. One pass if no ordering required.
GroupAggregate - Requires sorted input on group keys; pairs with Sort or index scan that emits ordered rows.
Sort - Quicksort in memory up to work_mem; external merge to disk when larger.
Each sort or hash in the same query can allocate up to work_mem (not shared across nodes in one query).

Plan Nodes to Watch

Node	Meaning
`HashAggregate`	In-memory grouping via hash table
`GroupAggregate`	Groups presorted input
`Sort Method: quicksort Memory`	Sort fit in `work_mem`
`Sort Method: external merge Disk`	Sort spilled

SQL Notes

-- Reduce sort work: match index order
CREATE INDEX orders_customer_total_idx ON orders (customer_id, total);
 
EXPLAIN (ANALYZE)
SELECT customer_id, sum(total)
FROM orders
GROUP BY customer_id;

Gotchas

Raising work_mem globally - Ten concurrent queries each using 256MB can OOM the host. Fix: Set per-role work_mem; keep global modest.
Assuming hash aggregate always wins - Some plans need sorted output for window functions. Fix: Read whether parent node requires order.
Distinct via sort - SELECT DISTINCT on wide rows sorts everything. Fix: DISTINCT ON with supporting index, or rewrite.
Ignoring HashAggregate spill - Rare but possible on huge distinct counts. Fix: Increase work_mem for batch role or pre-aggregate.
Window functions plus sort - Multiple sorts stack costs. Fix: Partition data, use partial indexes, or move to replica.

Alternatives

Alternative	Use When	Don't Use When
Incremental materialized view	Repeated heavy aggregates	Need real-time totals
Partial GROUP BY in app	Tiny result cardinality	Large distinct groups
BRIN + time filter	Rolling window on time series	Arbitrary dimension groups

FAQs

How much work_mem do I need?

Start from EXPLAIN ANALYZE spill indicators. Increase in small steps per workload role, not globally.

HashAggregate vs GroupAggregate?

Hash does not need sorted input. Group uses sorted input and can pipeline from index scans.

Why external merge on SSD still hurts?

Disk sorts add latency and CPU even on fast storage. Memory fits are dramatically cheaper.

Does LIMIT help GROUP BY?

Only with optimizations like sorted group aggregate early stop in specific plans. Do not assume.

Can indexes remove sorts?

Yes when scan order matches ORDER BY or GROUP BY leading keys.

What about parallel aggregate?

Large scans may use Partial Aggregate plus Gather. Each worker has its own work_mem budget.

How do I spot hash table spill?

Hash Join shows batches; HashAggregate spill is less common but check temp file usage in EXPLAIN ANALYZE.

Does work_mem affect index creation?

maintenance_work_mem controls index builds and VACUUM, not query sorts.

temp_file_limit role?

Cap runaway sorts per role to protect shared storage on ad hoc SQL pools.

Next reading?

work_mem tuning in OLTP vs OLAP section for role-specific defaults.

Nested Loop, Hash Join, Merge Join - Hash join spills
work_mem & sort/hash tuning - Role defaults
Workload Basics - OLTP vs batch memory
EXPLAIN Basics - Reading buffer lines

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Sort & Hash Aggregates

Sorts and hash aggregates need RAM up to work_mem per plan node per operation. When memory is insufficient, PostgreSQL 18.4 spills to disk and runtime jumps.

Recipe

Quick-reference recipe card - copy-paste ready.

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, count(*), sum(total)
FROM orders
GROUP BY customer_id
ORDER BY sum(total) DESC
LIMIT 20;

When to reach for this: Aggregation or ORDER BY queries show high I/O or external merge / Disk in the plan.

Working Example

CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  total numeric(12,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
 
INSERT INTO orders (customer_id, total)
SELECT (random() * 50000)::bigint, (random() * 1000)::numeric(12,2)
FROM generate_series(1, 1000000);
 
ANALYZE orders;
 
SET work_mem = '1MB';
 
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, sum(total) AS revenue
FROM orders
GROUP BY customer_id
ORDER BY revenue DESC;
 
RESET work_mem;

Look for HashAggregate vs GroupAggregate, and Sort Method: external merge Disk when work_mem is tiny.

What this demonstrates:

Hash aggregate preferred when sorting is not required
Sort spill when work_mem is exceeded
Why OLTP and OLAP roles need different work_mem

Deep Dive

How It Works

HashAggregate - Builds a hash table keyed by GROUP BY columns. One pass if no ordering required.
GroupAggregate - Requires sorted input on group keys; pairs with Sort or index scan that emits ordered rows.
Sort - Quicksort in memory up to work_mem; external merge to disk when larger.
Each sort or hash in the same query can allocate up to work_mem (not shared across nodes in one query).

Plan Nodes to Watch

Node	Meaning
`HashAggregate`	In-memory grouping via hash table
`GroupAggregate`	Groups presorted input
`Sort Method: quicksort Memory`	Sort fit in `work_mem`
`Sort Method: external merge Disk`	Sort spilled

SQL Notes

-- Reduce sort work: match index order
CREATE INDEX orders_customer_total_idx ON orders (customer_id, total);
 
EXPLAIN (ANALYZE)
SELECT customer_id, sum(total)
FROM orders
GROUP BY customer_id;

Gotchas

Raising work_mem globally - Ten concurrent queries each using 256MB can OOM the host. Fix: Set per-role work_mem; keep global modest.
Assuming hash aggregate always wins - Some plans need sorted output for window functions. Fix: Read whether parent node requires order.
Distinct via sort - SELECT DISTINCT on wide rows sorts everything. Fix: DISTINCT ON with supporting index, or rewrite.
Ignoring HashAggregate spill - Rare but possible on huge distinct counts. Fix: Increase work_mem for batch role or pre-aggregate.
Window functions plus sort - Multiple sorts stack costs. Fix: Partition data, use partial indexes, or move to replica.

Alternatives

Alternative	Use When	Don't Use When
Incremental materialized view	Repeated heavy aggregates	Need real-time totals
Partial GROUP BY in app	Tiny result cardinality	Large distinct groups
BRIN + time filter	Rolling window on time series	Arbitrary dimension groups

FAQs

How much work_mem do I need?

Start from EXPLAIN ANALYZE spill indicators. Increase in small steps per workload role, not globally.

HashAggregate vs GroupAggregate?

Hash does not need sorted input. Group uses sorted input and can pipeline from index scans.

Why external merge on SSD still hurts?

Disk sorts add latency and CPU even on fast storage. Memory fits are dramatically cheaper.

Does LIMIT help GROUP BY?

Only with optimizations like sorted group aggregate early stop in specific plans. Do not assume.

Can indexes remove sorts?

Yes when scan order matches ORDER BY or GROUP BY leading keys.

What about parallel aggregate?

Large scans may use Partial Aggregate plus Gather. Each worker has its own work_mem budget.

How do I spot hash table spill?

Hash Join shows batches; HashAggregate spill is less common but check temp file usage in EXPLAIN ANALYZE.

Does work_mem affect index creation?

maintenance_work_mem controls index builds and VACUUM, not query sorts.

temp_file_limit role?

Cap runaway sorts per role to protect shared storage on ad hoc SQL pools.

Next reading?

work_mem tuning in OLTP vs OLAP section for role-specific defaults.

Nested Loop, Hash Join, Merge Join - Hash join spills
work_mem & sort/hash tuning - Role defaults
Workload Basics - OLTP vs batch memory
EXPLAIN Basics - Reading buffer lines

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

Sort & Hash Aggregates

Buscar en la documentación

Sort & Hash Aggregates