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

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

FAQs

Related

• 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+.