PostgreSQL 10 High Performance

Group

Yet another node type left behind by progress, the GROUP node was the way earlier PostgreSQL versions implemented GROUP BY. It required that the input data was sorted by the grouping column set. As of PostgreSQL 7.4, that's commonly done by a HashAggregate instead:

EXPLAIN ANALYZE SELECT state,COUNT(*) FROM customers GROUP BY state;
QUERY PLAN
----------
HashAggregate  (cost=776.00..776.64 rows=51 width=3) (actual time=85.793..85.916 rows=52 loops=1)
       ->  Seq Scan on customers  (cost=0.00..676.00 rows=20000 width=3) (actual time=0.010..33.447 rows=20000 loops=1)
     Total runtime: 86.103 ms  

You can once again see the old behavior by turning off this optimization:

SET enable_hashagg=off;
EXPLAIN ANALYZE SELECT state,count(*) FROM customers ...