How GROUP BY Works — Under the Hood

setup

The query we're running

We want to know how many rides each driver completed and their total revenue. Step through to see what PostgreSQL does internally to produce the answer.

psql

SELECT driver_name, COUNT(*) AS rides, SUM(fare_amount) AS revenue FROM rides GROUP BY driver_name ORDER BY revenue DESC;

💡 PostgreSQL processes this in a few internal phases — FROM (read rows), GROUP BY (hash rows into buckets), aggregate (run COUNT/SUM once per bucket), then ORDER BY (sort the output). Step through each one.

phase 1 — FROM

Read all matching rows from the table

PostgreSQL starts by reading every row from the rides table — raw and unsorted, exactly as they sit on disk. No grouping yet.

driver_name	fare_amount	status
Ramesh Shrestha	350.00	completed
Sita Rai	210.00	completed
Ramesh Shrestha	480.00	completed
Bikash Thapa	320.00	cancelled
Sita Rai	290.00	completed
Ramesh Shrestha	150.00	completed
Bikash Thapa	410.00	completed

ℹ️ 7 raw rows. Ramesh appears 3 times, Sita twice, Bikash twice — all interleaved in disk order. GROUP BY hasn't happened yet.

phase 2 — group rows by key

Route each row into a bucket by hashing the key

As PostgreSQL scans each row, it computes hash(driver_name) and uses that value to find — or create — the matching bucket in memory. This strategy is called a HashAggregate. Rows go straight to their bucket — no sorting needed first.

driver_name	fare_amount	hash(driver_name)
Ramesh Shrestha	350.00	→ bucket R
Sita Rai	210.00	→ bucket S
Ramesh Shrestha	480.00	→ bucket R
Bikash Thapa	320.00	→ bucket B
Sita Rai	290.00	→ bucket S
Ramesh Shrestha	150.00	→ bucket R
Bikash Thapa	410.00	→ bucket B

🔑 The rows are still in their original disk order — Ramesh, Sita, Ramesh, Bikash, Sita, Ramesh, Bikash. PostgreSQL never reordered them. It just computed hash(driver_name) on the fly and dropped each row into its bucket as it scanned past.

▶ When does PostgreSQL sort instead of hashing?

HashAggregate is the typical default for a plain GROUP BY, but the planner can choose a GroupAggregate (sort-based) plan instead when:

An index already returns rows pre-sorted by the GROUP BY column — sorting is then free, so PostgreSQL just streams matching rows into each group as it scans.
The query also needs ORDER BY on the same column(s) — one sort can satisfy both the grouping and the final order.
There are so many distinct groups that the hash table would exceed work_mem — sorting and streaming avoids keeping all groups in memory at once.

Check which plan was chosen with EXPLAIN — look for HashAggregate vs GroupAggregate in the output.

phase 3 — buckets are ready

Each bucket holds every row for one group

After one pass over the table, PostgreSQL has built three in-memory buckets — one per distinct driver_name hash value. Each bucket holds every row that hashed into it. Aggregate functions run next, once per bucket.

Ramesh Shrestha 3 rows

350.00 · completed

480.00 · completed

150.00 · completed

Bikash Thapa 2 rows

320.00 · cancelled

410.00 · completed

Sita Rai 2 rows

210.00 · completed

290.00 · completed

ℹ️ 3 buckets for 3 distinct driver names. This is why more distinct values = more output rows. The aggregate functions haven't run yet — buckets just hold the raw rows.

phase 4 — aggregate each bucket

Run aggregate functions once per bucket

PostgreSQL runs COUNT(*) and SUM(fare_amount) on each bucket independently. Every bucket collapses into exactly one output row — this is where many rows become one summary row per group.

driver_name	rides (COUNT)	revenue (SUM)
Ramesh Shrestha	3	980.00
Bikash Thapa	2	730.00
Sita Rai	2	500.00

✓ 7 input rows collapsed to 3 output rows — one per bucket. COUNT(*) counted the rows in each bucket, SUM(fare_amount) added the fares. This is the core mechanic of every aggregate query.

phase 5 — ORDER BY

Sort the aggregated result

ORDER BY revenue DESC runs last — after aggregation. This is why you can ORDER BY an aggregate like SUM(fare_amount), but you cannot WHERE on it — WHERE runs before the groups exist.

#	driver_name	rides	revenue ↓
1	Ramesh Shrestha	3	980.00
2	Bikash Thapa	2	730.00
3	Sita Rai	2	500.00

✓ Final result — 3 rows, sorted by revenue descending. Full pipeline: read rows → hash into buckets → aggregate → sort output.

⚠️ The casing trap: what if one row had "ramesh shrestha" (lowercase)? hash('ramesh shrestha') ≠ hash('Ramesh Shrestha') — it hashes to a completely different bucket, creating a 4th group regardless of where that row sits in the table. That's exactly why a casing mismatch silently splits one driver into two rows in your output.

The full pipeline — one summary

Phase	What PostgreSQL does	Output
FROM	Read all rows from disk in storage order	7 raw rows
HashAggregate	Hash each `driver_name`, drop row into matching bucket	3 in-memory buckets
Aggregate	Run `COUNT(*)` and `SUM()` once per bucket	3 result rows
ORDER BY	Sort the 3 result rows by `revenue DESC`	Final 3 rows

How GROUP BY works — under the hood