Investigating performance

Grouping sets is 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)

In PostgreSQL, hash aggregates are only supported for normal GROUP BY clauses involving no grouping sets. In PostgreSQL 10.0, the planner already has more options than in PostgreSQL 9.6. Expect grouping sets to be faster in the new version.