Why does Postgres run SORT on many rows, before GROUP BY? I'm using Postgres 9.4, with a recently analyzed database. These are my tables:

Materialized view "public.vw_chemical_summary_by_ccg"
     Column      |         Type         | Modifiers
-----------------+----------------------+-----------
 processing_date | date                 |
 pct_id          | character varying(3) |
 chemical_id     | character varying(9) |
 items           | bigint               |
 cost            | double precision     |
Indexes:
    "vw_idx_chem_by_ccg_chem_id" btree (chemical_id)
    "vw_idx_chem_by_ccg_chem_id_vc" btree (chemical_id varchar_pattern_ops)
    "vw_idx_chem_by_ccg_joint_id" btree (pct_id, chemical_id)

               Table "public.frontend_pct"
      Column       |          Type           | Modifiers
-------------------+-------------------------+-----------
 code              | character varying(3)    | not null
 name              | character varying(200)  |
 org_type          | character varying(9)    | not null
Indexes:
    "frontend_pct_pkey" PRIMARY KEY, btree (code)
    "frontend_pct_code_1df55e2c36c298b2_like" btree (code varchar_pattern_ops)


This is my query: 

EXPAIN (ANALYSE, BUFFERS) 
SELECT pr.pct_id AS row_id, pc.name AS row_name, 
       pr.processing_date AS date, SUM(pr.cost) AS actual_cost, 
       SUM(pr.items) AS items 
FROM vw_chemical_summary_by_ccg pr 
JOIN frontend_pct pc 
  ON pr.pct_id=pc.code AND pc.org_type='CCG' 
GROUP BY row_id, row_name, date 
ORDER BY date, row_id;


The results of the analyse shows a very slow sort on 5 million rows, BEFORE running the GroupAggregate. 

The result of the GroupAggregate is just 5,000 rows. So wouldn't it make more sense to aggregate first, then sort?

Explain here: http://explain.depesz.com/s/IS1

Any other suggestions for speeding up the query would also be very welcome.
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.