Why does Postgres run SORT on many rows, before GROUP BY?
I ran into the same situation, a large number of rows of raw data being sorted on disk in order to be fed into a GroupAggregate which drastically reduced the number of rows.

I tried removing the ORDER BY clause; that allowed the optimizer to choose HashAggregate, which was significantly faster. To give a picture of the speed increase, a particular subset of my data took about


13.9 seconds using the original Sort and GroupAggregate
9.4 seconds for a simple EXPLAIN ANALYZE SELECT without a GROUP BY clause
9.6 seconds using the unsorted HashAggregate


It also scaled similarly with more data. I'm only using about a million rows, so you may still need a higher work_mem for your larger dataset (I am using 10MB). Although http://www.depesz.com/2013/05/09/explaining-the-unexplainable-part-3/#hash-aggregate suggests that HashAggregate will go to disk if it doesn't fit in memory, I found that reducing work_mem just prompted the planner to revert to the Sort/GroupAggregate plan.

I added SELECT * FROM (&lt;unsorted query&gt;) AS nested ORDER BY 1,2,3 to the outside and this sorts the aggregated output to give the same results as the original query, while still using the fast HashAggregate. I think there is perhaps an optimization opportunity here that the planner is not taking.

I tested this on 8.4 and 9.3 and got the same results with both.

Update:

If one of the aggregates being calculated is a COUNT(DISTINCT &lt;fields&gt;), then apparently HashAggregate can not be used.