Investigating performance

Grouping sets are a powerful feature; they help to reduce the number of expensive queries. Internally, PostgreSQL will basically turn to traditional GroupAggregates to make things work. A GroupAggregate node requires sorted data, so be prepared that PostgreSQL might do a lot of temporary sorting:

test=# explain SELECT   region, country, avg(production)                                                                                         FROM    t_oil                                                                                                                                    WHERE   country IN ('USA', 'Canada', 'Iran', 'Oman')                                                                                             GROUP BY GROUPING SETS ( (), region, country); 
                            QUERY PLAN                                 
--------------------------------------------------------------- 
 GroupAggregate  (cost=22.58..32.69 rows=34 width=52) 
   Group Key: region 
   Group Key: () 
   Sort Key: country 
     Group Key: country 
   ->  Sort  (cost=22.58..23.04 rows=184 width=24) 
         Sort Key: region 
         ->  Seq Scan on t_oil   
              (cost=0.00..15.66 rows=184 width=24) 
              Filter: (country = ANY  
                 ('{USA,Canada,Iran,Oman}'::text[])) 
(9 rows)

Hash aggregates are only supported for normal GROUP BY clauses involving no grouping sets. According to the developer of grouping sets (Atri Shama), whom I talked to shortly before writing this chapter, adding support for hashes is not worth the effort; so it seems PostgreSQL already has an efficient implementation even if the optimizer has fewer choices than it has with normal GROUP BY statements.